What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
All number rows in a window order. ROW_NUMBER gives unique numbers with no ties, RANK gives ties the same number and then skips ahead, and DENSE_RANK gives ties the same number without skipping.
Explain it simply
Think of a class result. If two students tie for second place, what is the next student's position? With RANK, they are fourth (positions 2, 2, then 4). With DENSE_RANK, they are third (2, 2, 3). ROW_NUMBER does not allow ties at all; it just numbers rows 1, 2, 3, 4, breaking ties arbitrarily unless you add a tie-breaker.
Example
SELECT name, marks,
ROW_NUMBER() OVER (ORDER BY marks DESC) AS rn,
RANK() OVER (ORDER BY marks DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY marks DESC) AS drnk
FROM results;
-- name | marks | rn | rnk | drnk
-- Asha | 95 | 1 | 1 | 1
-- Rohit | 90 | 2 | 2 | 2
-- Meera | 90 | 3 | 2 | 2
-- Karan | 85 | 4 | 4 | 3Use ROW_NUMBER when you need exactly one row per group (for example latest record). Use DENSE_RANK for "second highest salary" questions, since ties should not hide the next distinct value.
What to say in the interview
"ROW_NUMBER is always unique, RANK shares a number on ties and leaves gaps, DENSE_RANK shares and leaves no gaps. For nth-highest-value questions I use DENSE_RANK, and for picking one row per group I use ROW_NUMBER with a deterministic ORDER BY."
Common mistakes
- Using ROW_NUMBER for "second highest" and missing tied values.