RANK vs DENSE_RANK vs ROW_NUMBER in SQL
Compare RANK, DENSE_RANK and ROW_NUMBER in SQL — how each handles ties and gaps, with a side-by-side example and guidance on when to use each function.
Expected Interview Answer
ROW_NUMBER assigns a unique sequential number to every row, RANK gives tied rows the same number but skips the next values, and DENSE_RANK gives ties the same number without skipping.
All three are ranking window functions used with OVER (ORDER BY ...) and optional PARTITION BY. ROW_NUMBER never repeats — even identical values get distinct numbers in an arbitrary order. RANK and DENSE_RANK both assign equal ranks to ties, but RANK leaves gaps after a tie (1, 1, 3) while DENSE_RANK keeps the sequence continuous (1, 1, 2). The right choice depends on whether you want unique row IDs, competition-style ranking with gaps, or gap-free tiers.
- ROW_NUMBER gives a guaranteed unique ordinal per row
- RANK reflects standard competition ranking with gaps
- DENSE_RANK produces gap-free ranking tiers
- All handle ties predictably via ORDER BY
- PARTITION BY lets ranks reset per group
AI Mentor Explanation
Three batters all score 50. ROW_NUMBER still forces an order — 1, 2, 3 — even though scores tie. RANK calls all three joint 1st then jumps to 4th for the next batter. DENSE_RANK also makes them joint 1st but the next batter is 2nd, keeping the ladder continuous with no missing positions.
Step-by-Step Explanation
Step 1
Set the ordering
Provide OVER (ORDER BY column) so the functions know how to sequence rows before numbering them.
Step 2
Choose ROW_NUMBER for uniqueness
Use ROW_NUMBER when you need a distinct ordinal per row, e.g. pagination or picking one row per group.
Step 3
Choose RANK for competition ranking
Use RANK when ties should share a position and the next value should skip, reflecting how many rows ranked higher.
Step 4
Choose DENSE_RANK for gap-free tiers
Use DENSE_RANK when ties share a position but you want the next rank to continue without gaps.
Step 5
Partition if needed
Add PARTITION BY to restart the numbering within each group, such as ranking salaries per department.
What Interviewer Expects
- Knows ROW_NUMBER is always unique
- Explains RANK skips numbers after ties
- Explains DENSE_RANK does not skip after ties
- Mentions OVER (ORDER BY) and PARTITION BY usage
- Gives a scenario where each is the correct choice
Common Mistakes
- Thinking RANK and DENSE_RANK behave identically on ties
- Assuming ROW_NUMBER handles ties like RANK
- Forgetting ORDER BY inside OVER, making results nondeterministic
- Using ROW_NUMBER when a gap-free tier ranking was needed
- Not partitioning when ranks should reset per group
Best Answer (HR Friendly)
“These three functions all number rows in order. ROW_NUMBER always gives a unique number, RANK gives tied rows the same number but then jumps ahead, and DENSE_RANK gives ties the same number without leaving any gaps. You pick based on how you want ties handled.”
Code Example
SELECT
name,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
RANK() OVER (ORDER BY score DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM players;
-- Example output for scores 90, 90, 80:
-- name score row_num rnk dense_rnk
-- Ann 90 1 1 1
-- Bob 90 2 1 1
-- Cara 80 3 3 2Follow-up Questions
- When would you prefer DENSE_RANK over RANK?
- How do you get the top N rows per group using these functions?
- Why is ORDER BY required inside the OVER clause here?
- How does ROW_NUMBER help with pagination?
- What is NTILE and how does it relate to ranking?
MCQ Practice
1. For scores 90, 90, 80, what does RANK assign to the 80 row?
RANK skips after the two tied 1st places, so the next row gets rank 3.
2. For scores 90, 90, 80, what does DENSE_RANK assign to the 80 row?
DENSE_RANK does not skip after ties, so the 80 row gets rank 2.
3. Which function always produces unique values with no ties?
ROW_NUMBER assigns a distinct sequential number to every row regardless of tied values.
Flash Cards
ROW_NUMBER behavior on ties? — Always unique — tied values still get different sequential numbers.
RANK behavior on ties? — Ties share the same rank, then the next value skips (1, 1, 3).
DENSE_RANK behavior on ties? — Ties share the same rank with no gap after (1, 1, 2).
What do all three require? — An OVER clause with ORDER BY; PARTITION BY optionally resets ranking per group.