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;

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;
Ram
Ram

I'm a full-stack developer and a software enthusiast who likes to play around with cloud and tech stack out of curiosity. You can connect with me on Medium, Twitter or LinkedIn.

Leave a Reply

Your email address will not be published. Required fields are marked *