SQL ROW_NUMBER vs RANK vs DENSE_RANK | What Happens When Rows Tie?
What is the real difference between ROW_NUMBER(), RANK(), and DENSE_RANK() in SQL?
The answer becomes clear when rows tie.
In this lesson, we start with a practical business question:
Two providers have the same paid amount. Should they receive the same rank? And if they do, should the next rank skip a number?
Using SQL Server and healthcare claims examples, we compare all three ranking functions side by side.
The key patterns are:
ROW_NUMBER() → 1, 2, 3, 4
RANK() → 1, 2, 2, 4
DENSE_RANK() → 1, 2, 2, 3
What you'll learn
How ROW_NUMBER() handles ties
How RANK() handles ties and creates gaps
How DENSE_RANK() handles ties without gaps
When ROW_NUMBER() is the right business choice
When RANK() is appropriate
When DENSE_RANK() is appropriate
Why adding a tie-breaker can accidentally destroy a meaningful tie
How to rank providers using real claims data
Top 3 rows vs Top 3 ranks
Why ranking functions can return different numbers of rows
How to choose the correct ranking function from the business requirement
Mental model
Need one unique position? → ROW_NUMBER()
Ties should share rank and gaps matter? → RANK()
Ties should share rank with no gaps? → DENSE_RANK()
The most important question is not:
“Which SQL function should I use?”
It is:
“What does the business want to happen when rows tie?”
One important warning: if ties are meaningful to the business, do not automatically add another column to the window ORDER BY. A tie-breaker can turn tied rows into different ranking values.
Database: HealthcareWindowFunctionsDB
Platform: Microsoft SQL Server 2022
All healthcare data used in this lesson is synthetic and for educational purposes.
Next: NTILE() — buckets, quartiles, and segmentation.
#SQL #SQLServer #WindowFunctions #ROWNUMBER #RANK #DENSERANK #dataanalytics
00:00 ROW_NUMBER vs RANK vs DENSE_RANK
01:25 The real tie problem
02:05 Compare all 3 functions side by side
03:05 When to use ROW_NUMBER()
03:50 When to use RANK()
04:35 When to use DENSE_RANK()
05:20 Rank providers using real claims data
06:15 Make ties visible in real data
07:05 Top 3 rows vs Top 3 ranks
08:15 Practice challenge — rank providers
09:00 Which ranking function should you use?
09:30 Key takeaways