What is the difference between ROW_NUMBER, RANK, and DENSE_RANK? When would you choose each?

How to think about it

All three stamp an integer rank on each row using an ORDER BY inside a window. The only thing that differs is what happens at a tie — and getting that wrong in a “find the Nth highest salary” question is one of the most common live-coding mistakes. So it’s worth understanding the tie behaviour, not memorising a table.

A worked example — all three on tied data

Five employees, with ties at 90,000 and at 80,000. Watch the three columns diverge exactly where the ties sit:

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
ORDER BY salary DESC, name;

name	salary	row_num	rnk	dense_rnk
Alice	90000	1	1	1
Bob	90000	2	1	1
Carol	80000	3	3	2
Dave	80000	4	3	2
Eve	70000	5	5	3

Read across the tied pairs. ROW_NUMBER never ties — Alice and Bob get 1 and 2 (the engine breaks the tie arbitrarily). RANK gives both 1, then skips to 3 for Carol, and skips again to 5 for Eve — the gaps equal the tie counts. DENSE_RANK gives the ties the same number but never gaps: 1, 1, 2, 2, 3.

When to reach for each

• ROW_NUMBER — when you need exactly N rows, no exceptions: deduplication (rn = 1), pagination (rn BETWEEN 11 AND 20), top-N-per-group.
• RANK — sports-style ranking: two golds, no silver. Leaderboards where ties share a place and the next place is skipped to reflect it.
• DENSE_RANK — “Nth distinct value” problems. You want the 3rd salary level, not the 3rd row, so the consecutive numbering is essential:

SELECT name, salary
FROM (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
) ranked
WHERE dr = 2;   -- the second-highest salary, ties-safe