Difference between ROW_NUMBER(), RANK() and DENSE_RANK() in SQL

The main difference between ROW_NUMBER() and RANK() in SQL server is that ROW_NUMBER doesn't handle ties while RANK() does. Though both ROW_NUMBER() and RANK() are window function used for ranking row, the way they handle duplicates differentiate them. ROW_NUMBER() function assigns a unique number to each row starting from 1 and arbitrarily assign values if two rows are equal, which means it's not guaranteed that which row will get 2nd or 3rd position if they are identical. In contrast, RANK() assigns an equivalent rank to similar rows, which creates gaps between RANK. 

For example, if you have 4 rows out of which 3 has the same value and 1 has a different value than ROW_NUMBER() will assign them 1,2, 3, and 4, rank() will assign them 1, 1, 1 and 4.

So you can see the gap in ranking when you use rank(), this gap can be eliminated by using DENSE_RANK() which assigns the same rank to equal rows but assign next number or rank in sequence to next unequal rows.

For example, in our case those four rows will get dense rank 1, 1, 1 and 2. So there is no gap between ranks. If you look closely, it's actually the same as using ROW_NUMBER with unique rows or using distinct with ROW_NUMBER. 

The difference between RANK, DENSE_RANK, and ROW_NUMBER can be better understood by a simple example, which we will see in the next section.

What is ROW_NUMBER(), RANK() and DENSE_RANK() function in SQL

In SQL Server, rank(), dense_rank(), and row_number() are window functions used for ranking rows within a result set based on specified criteria. They can help you analyze and organize data in various ways.

These are called window function because they operates on a window of rows and provides ranking over a partition. 

For example, if you have a Orders table which holds multiple orders by a customer, by using these function, you can partition result set and assign a ranking to each row for a particular customer.

Most of the modern database support windows function, including major three MySQL, Microsoft SQL Server, and Oracle, and we will use SQL Server for our example.

Here's an explanation of the differences with examples:

1. ROW_NUMBER()

The ROW_NUMBER() function in SQL Server assigns a unique rank to each row within the result set without considering duplicate values. Each row gets a unique, incrementing rank value.

Example:

SELECT Name, Score, 

ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNum
FROM Students;

Name	Score	RowNum
Alice	95	1
Bob	90	2
Carol	90	3
Dave	85	4

You can see, here, each row has a unique rank value based on their position in the ordered result set because ROW_NUMBER assigns a unique number to each row starting from 1 but doesn't handle ties and assign arbitrary ranking for equal rows. 

2. RANK()

The RANK() function in SQL assigns a unique rank to each row within the result set, with gaps in ranking values when there are duplicate values in the order. Rows with the same values get the same rank, and the next rank is skipped.

Example:

SELECT Name, Score, 

RANK() OVER (ORDER BY Score DESC) AS Rank
FROM Students;

Name	Score	Rank
Alice	95	1
Bob	90	2
Carol	90	2
Dave	85	4

In this example, Alice has the highest score and gets rank 1, while Bob and Carol share rank 2 because they have the same score. Dave is ranked 4 because there are three rows with higher scores.

3. DENSE_RANK()

The final one in the list is DENSE_RANK() which is similar to RANK(), but it assigns the same rank to rows with the same values in the order, and there are no gaps in ranking values. 

For example, in the previous example if you had used DENSE_RANK() then Dave would have got the 3rd rank instead of 4th as there won't be any gap between 2nd and 4th Ranking. 

Example:

SELECT Name, Score, 
DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank
FROM Students;

Name	Score	DenseRank
Alice	95	1
Bob	90	2
Carol	90	2
Dave	85	3

Here, Bob and Carol share rank 2, and Dave got 3rd rank insead of 4th like in previous example, hence, there are no gaps in ranking values.

Here is also a nice diagram which explains the difference between rank, dense_rank, and row_number clearly with a single SQL query result

You can see that row_number gave unique number to all the rows irrespective of their value but rank give same rank to same values and has gap in ranking like there is no 5th ranking, but dense_rank eliminate that gap and give 5th ranking to next value. 

That's all about the difference between RANK(), DENSE_RANK() And ROW_NUMBER() in SQL Server.  RANK(), DENSE_RANK(), and ROW_NUMBER() are all SQL Server window functions for ranking rows, but they differ in how they handle duplicate values and gaps in ranking. 

RANK() assigns unique ranks with gaps, DENSE_RANK() assigns unique ranks without gaps, and ROW_NUMBER() assigns unique row numbers without considering duplicate values. 

The choice of which to use depends on the specific analysis and requirements of your SQL query.

Other SQL Server articles and Interview Questions you may like to explore

• 5 Free Oracle and SQL Server courses for Programmers (courses)
• Difference between coalesce() and isnull in SQL (AnsweR)
• Difference between Cast, Convert, and Parse in SQL Server? (answer)
• The right way to compare dates in SQL query? (example)
• How to convert the result of a SELECT command into a CSV String? (example)
• How to remove duplicate rows from a table in SQL? (solution)
• How to split String in SQL Server? (answer)
• 5 Websites to learn SQL online for FREE? (resource)
• How to create an Identity column in SQL Server? (example)
• Difference between rank(), row_number(), and dense_rank() in SQL? (answer)
• How to find all customers who have never ordered? (solution)
• How to find the length of a String in SQL Server? (example)
• What is the difference between close and deallocate a cursor? (answer)
• How to add columns to an existing table in MSSQL? (example)

Thanks for reading this article so far. If you like this SQL interview question and my explanation then please share it with your friends and colleagues. If you have any questions or feedback then please drop a note. 

And lastly one question for? What was the last SQL question asked to you on interview?