The previous lesson's salary_rank gave two equally-paid employees two different numbers, which is misleading if they're genuinely tied for, say, 2nd place. RANK() and DENSE_RANK() both fix that, by letting ties share a rank, they just disagree on what happens to the numbering right after a tie.
Learning Objectives
- Explain how RANK() and DENSE_RANK() both handle ties differently from ROW_NUMBER().
- Explain the one real difference between RANK() and DENSE_RANK() themselves: gaps after a tie.
- Choose correctly between all three ranking functions for a given real requirement.
Tied Values
Two employees, same salary
RANK() or DENSE_RANK()?
Both let the tie share a rank
Next Rank
Gap (RANK) or no gap (DENSE_RANK)
Correct Ranking
Matches the real-world meaning of a tie
All Three Ranking Functions, Side by Side
Picture three employees with salaries 90000, 85000, 85000, and 80000, ordered highest to lowest. Here's what each function actually produces:
| Salary | ROW_NUMBER() | RANK() | DENSE_RANK() |
|---|---|---|---|
| 90000 | 1 | 1 | 1 |
| 85000 | 2 | 2 | 2 |
| 85000 | 3 | 2 | 2 |
| 80000 | 4 | 4 | 3 |
The two 85000 rows tie under RANK() and DENSE_RANK() (both give them 2), but ROW_NUMBER() still forces them apart (2 and 3). The real difference between RANK() and DENSE_RANK() shows up on the very next row: RANK() jumps to 4, leaving a gap where the "missing" rank 3 would have been, DENSE_RANK() continues at 3, with no gap at all.
RANK() and DENSE_RANK() in a Query
SELECT first_name, last_name, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_dense_rank
FROM employees;Try It Yourself
Using the table above (90000, 85000, 85000, 80000), what would a fifth employee earning 80000 get under RANK() and under DENSE_RANK()?
RANK(): 4 (tied with the first 80000 row, both get 4) DENSE_RANK(): 3 (tied with the first 80000 row, both get 3)
Frequently Asked Questions
Which one should "rank" normally mean, RANK() or DENSE_RANK()?
RANK() matches how rankings usually work in the real world, like a race: two people tied for 2nd place, the next person is genuinely 4th, not 3rd, because two people already occupy spots 2 and 3's worth of standing. DENSE_RANK() is better suited to counting distinct tiers or levels, where the gap left by RANK() would be meaningless, like quartile or grade boundaries.
Can RANK() and DENSE_RANK() ever produce the same result as each other?
Yes, whenever there are no ties at all in the data being ranked, all three functions, ROW_NUMBER(), RANK(), and DENSE_RANK(), produce identical results, 1, 2, 3, 4... The difference only ever shows up once an actual tie exists.
Common Beginner Mistakes
Using RANK() when DENSE_RANK()'s no-gap behavior was actually needed
If a report is counting "how many distinct salary tiers exist," RANK()'s gaps after a tie will overcount or misrepresent that, DENSE_RANK() is the one that actually counts distinct tiers correctly, since it never skips a number.
Assuming ROW_NUMBER() and RANK() are interchangeable when there happen to be no ties yet
They produce identical output with no ties present, but the moment duplicate values show up in the data, which rows end up tied changes everything, ROW_NUMBER() still forces them apart while RANK() lets them share. Choose based on whether ties should ever be allowed to share a rank, not based on today's data happening to have none.
Best Practices
- Use RANK() when the real-world meaning of a tie should leave a gap afterward, like a competitive ranking.
- Use DENSE_RANK() when counting distinct levels or tiers, where a gap would misrepresent the count.
- Use ROW_NUMBER() when every row genuinely needs a distinct position, with ties broken arbitrarily or by an explicit tiebreaker.
Interview Questions
Explain the exact difference between RANK() and DENSE_RANK(), with a concrete example.
Both let tied rows share the same rank, the difference is what happens to the rank right after a tie. Given salaries 90000, 85000, 85000, 80000 ordered descending: RANK() produces 1, 2, 2, 4, it leaves a gap at 3 because two rows already occupy ranks 2 and 3's worth of position. DENSE_RANK() produces 1, 2, 2, 3, it never leaves a gap, counting only the number of distinct values seen so far.
If a report needs to count how many distinct salary levels exist in a department, which ranking function is correct, and why?
DENSE_RANK(), because its highest value for a given partition equals the count of distinct values present, with no gaps to throw that count off. RANK()'s highest value would instead reflect the number of rows, inflated by how many ties occurred, not the number of genuinely distinct salary levels.
Summary
RANK() and DENSE_RANK() both let tied rows share a rank, unlike ROW_NUMBER(). The one real difference between them: RANK() leaves a gap in the numbering right after a tie, DENSE_RANK() never does, continuing with the next consecutive number instead. Pick based on whether a gap after a tie is the correct real-world meaning, or whether counting distinct levels matters more.
What's Next?
SQL NTILE() is next: splitting a partition's rows into a fixed number of roughly equal-sized buckets, like salary quartiles.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →