"Who are our top 10 customers?" "What's the rank of each student in their class?" "Show me the 3 best-selling products per category."
These questions all require ranking data. SQL provides three powerful window functions for this, and choosing the right one matters.
The Three Ranking Functions
| Function | Handles Ties | Gaps After Ties |
|---|---|---|
ROW_NUMBER() | No - assigns unique numbers | N/A |
RANK() | Yes - same rank for ties | Yes - skips numbers |
DENSE_RANK() | Yes - same rank for ties | No - consecutive |
Let's see each in action.
Interactive Example: Student Scores
Look at Bob and Charlie - both scored 90:
- ROW_NUMBER: Bob=2, Charlie=3 (arbitrary order for ties)
- RANK: Both=2, Diana=4 (gap after tie)
- DENSE_RANK: Both=2, Diana=3 (no gap)
When to Use Each Function
ROW_NUMBER: Unique Numbering
Use when you need exactly one number per row, regardless of ties:
-- Pagination: Get rows 11-20
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY created_at) as rn
FROM posts
) WHERE rn BETWEEN 11 AND 20;
RANK: Competition-Style Ranking
Use for leaderboards where ties share rank but the next person "drops":
-- Olympic-style: Gold, Gold, Bronze (no Silver)
SELECT athlete, time,
RANK() OVER (ORDER BY time) as place
FROM race_results;
DENSE_RANK: Consecutive Ranking
Use when you want no gaps in your ranking sequence:
-- Top 3 salary levels (might be more than 3 people)
SELECT * FROM (
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
) WHERE salary_rank <= 3;
Ranking Within Groups: PARTITION BY
The real power comes when ranking within categories:
Notice how ranking restarts at 1 for each department!
Top N Per Group Pattern
One of the most useful SQL patterns - get the top N items from each category:
Practical Example: Sales Leaderboard
Quick Reference Table
| Scenario | Best Function |
|---|---|
| Pagination | ROW_NUMBER() |
| Leaderboard with gaps | RANK() |
| Top N salary levels | DENSE_RANK() |
| Exactly N rows per group | ROW_NUMBER() |
| Olympic medal ranking | RANK() |
| Finding duplicates | ROW_NUMBER() |
Common Mistakes to Avoid
-
Forgetting ORDER BY: Ranking without ORDER BY gives unpredictable results.
-
Using RANK for pagination: If there are ties, you might get more rows than expected.
-
Ignoring tie-breakers: Add secondary ORDER BY columns to control tie behavior:
ROW_NUMBER() OVER (ORDER BY score DESC, created_at ASC)
Conclusion
- ROW_NUMBER(): One unique number per row, perfect for pagination and deduplication.
- RANK(): Ties share rank, gaps follow - ideal for competitions.
- DENSE_RANK(): Ties share rank, no gaps - great for "top N levels" queries.
Add PARTITION BY to rank within groups, and you can solve almost any leaderboard or "top N per category" problem in a single query.