Difference between Row_Number, Rank, and Dense_Rank in SQL

A popular SQL interview questions on Window function from software developer interview

Hello guys, if you have done a software developer interview then you may know that window functions is quite a hot topic there and interviewers often like to ask about the difference between Row_Number, Rank, and Dense_Rank functions, I will explain what they are to you today.

In the past, I have talked about common system design questions like API Gateway vs Load Balancer and Horizontal vs Vertical Scaling, JWT vs Session based authentication, Forward proxy vs reverse proxy as well common System Design problems and in this article we will this interesting SQL questions.

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 functions used for ranking rows, the way they handle duplicates differentiates them.

ROW_NUMBER() function assigns a unique number to each row starting from 1 and arbitrarily assigns 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 then ROW_NUMBER() will assign them 1,2, 3, and 4, and 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 assigns the next number or ranks in sequence to the next unequal rows.

For example, in our case, those four rows will get dense ranks 1, 1, 1, and 2. So there is no gap between ranks. If you look closely, it’s 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.

Though, if you are doing interview prep in 2025 then I also suggest you check out sites like ByteByteGo, Design Guru, Exponent, Educative, Codemia.io, InterviewReddy.io and Udemy which have many great Coding interview System design courses for better prepration.

---

New Year Offer

I would like you to thank you for reading Javarevisited in 2024. We are now 35K strong which is a really big achievement and it wasn’t possible without your support.

As a token of our appreciation, we're offering you a limited-time offer of 35% off a paid subscription.

Instead of paying 50$ / year, you pay 32.5$ / year (only 3$ / month)!

Get 35% off forever

Here are the benefits you unlock with a paid subscription:

1. Get access to paid subscribers posts. 📗

2. Access to Full archive of more than 115+ posts 🏆

Many expense it with their team's learning budget

What is Row_Number(), Rank() and Dense_Rank() functions in MSSQL?

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 functions because they operate on a window of rows and provide ranking over a partition.

This is a paid post but I don’t want you to leave empty handed, if you are preparing for SQL interviews, you can download by Grokking the SQL Interview book, free sample copy where I have shared many popular SQL questions. You can also use code friends50 to get 50% discount on full copy.