RANK, DENSE_RANK, ROW_NUMBER: what is the simple difference?

What are RANK, DENSE_RANK and ROW_NUMBER in SQL Server? What are the differences between them and how to use them? Explain in simple terms.

In SQL SERVER, ROW_NUMBER returns row numbers for rows in a SELECT query without any skip in the serial order. It doesn’t depend on any custom criteria and is simply a serial number for each row returned by the query.

On the other hand, RANK returns the RANK for a single row in an executing query. It skips N-1 rows if there are duplicate rows for the data returned by a partition condition (based on which the records are grouped).

For example, the below query returns serialized numbers per department in the table. It skips numbering if there are duplicate rows in the selected rows.

SELECT RANK() OVER (PARTITION BY DEPT ORDER BY SAL) AS RANK FROM EMP;

DENSE_RANK is similar to RANK, but 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;

To summarize,

  • 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 and skips N-1 rows if there are duplicate rows
  • DENSE_RANK returns the RANK for the rows based on an executing query but doesn’t skip any ranks if there are duplicates in the grouping.
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.

Privacy Overview
Referbruv

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.

Strictly Necessary Cookies

Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings.

3rd Party Cookies

This website uses Google Analytics to collect anonymous information such as the number of visitors to the site, and the most popular pages.

Keeping this cookie enabled helps us to improve our website.