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 •  Added one month ago

Join the Newsletter

Subscribe to get our latest content by email.
    We won't send you spam. Unsubscribe at any time.
    We use cookies to provide you with a great user experience, analyze traffic and serve targeted promotions.   Learn More   Accept