what is the difference between RANK, DENSE_RANK and ROW_NUMBER?
In SQL SERVER:
- ROW_NUMBER returns row numbers for rows in a SELECT query without any skip in the serial order
- RANK returns the RANK for a single row in an executing query. RANK skips N-1 rows if there are duplicate rows for the data returned by a partition condition (based on which the records are grouped)
SELECT RANK() OVER (PARTITION BY DEPT ORDER BY SAL) AS RANK FROM EMP;
- DENSE_RANK returns the RANK for the rows based on an executing query. DENSE_RANK doesn't skip any ranks if there are duplicates in the grouping.
SELECT DENSE_RANK() OVER (PARTITION BY DEPT ORDER BY SAL) AS DRANK FROM EMP;
Posted Mar 19, 2021