Q16: What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?
All three are ranking window functions, but they handle tied values differently:
ROW_NUMBER(): Assigns a unique, sequential integer to every row (1, 2, 3, 4, 5). Ties receive arbitrary unique numbers.RANK(): Assigns identical rank values to tied rows, but skips subsequent ranks to reflect tie counts (1, 2, 2, 4, 5).DENSE_RANK(): Assigns identical rank values to tied rows without skipping subsequent numbers (1, 2, 2, 3, 4).
Ranking Behavior Comparison (Tied Values at 100)
Values:
[100, 100, 90, 80]Target ordered salary list containing duplicate top values.
ROW_NUMBER():
[1, 2, 3, 4]Non-repeating sequential assignment.
RANK():
[1, 1, 3, 4]Ties share rank 1; position 2 is skipped.
DENSE_RANK():
[1, 1, 2, 3]Ties share rank 1; next rank continues strictly sequentially.
Key Takeaways
- Use
DENSE_RANK()to find Nth highest values without missing sequential rank numbers. - Use
ROW_NUMBER()for pagination or non-deterministic duplicate row removal.