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;

SQL Posted Mar 19, 2021

You can now show your support. 😊

We use cookies to provide you with a great user experience, analyze traffic and serve targeted promotions.   Learn More   Accept