RANK vs DENSE_RANK on Ties
The classic question on gap vs no-gap ranking when values tie.
The Three Ranking Functions Side by Side
SQL gives you three ranking window functions, and the single most-asked window question is how they differ on ties:
- ROW_NUMBER — always unique; ties get arbitrary distinct numbers.
- RANK — ties share a rank, then the next rank skips (leaves a gap).
- DENSE_RANK — ties share a rank, then the next rank does not skip (no gap).
Being able to recite this distinction instantly is a reliable interview signal.
A Concrete Example Table
Imagine five scores ordered descending: 100, 90, 90, 80, 70. Watch how each function numbers them.
- ROW_NUMBER: 1, 2, 3, 4, 5
- RANK: 1, 2, 2, 4, 5 (skips 3 after the tie)
- DENSE_RANK: 1, 2, 2, 3, 4 (no skip)
Memorize this 100/90/90/80/70 example — it answers the question on the spot.
SELECT
score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rownum,
RANK() OVER (ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense
FROM scores;All lessons in this course
- OVER, PARTITION BY and ORDER BY
- ROW_NUMBER for Unique Sequencing
- RANK vs DENSE_RANK on Ties
- Filtering on a Window Result