What is the difference between ROW_NUMBER, RANK, and DENSE_RANK? When would you choose each?
ROW_NUMBER assigns a unique sequential integer to every row regardless of ties. RANK assigns the same number to tied rows but skips subsequent positions. DENSE_RANK also assigns the same number to ties but never skips positions.
How to think about it
All three produce an integer rank per row using an ORDER BY inside a window. The only thing that differs is what happens when two rows tie. Getting this wrong in a “find the Nth highest salary” question is one of the most common live-coding mistakes — so it’s worth really understanding the tie-breaking behavior, not just memorizing a table.
The tie-handling rule for each
| Function | Tied rows get | Next position after tie |
|---|---|---|
ROW_NUMBER | distinct numbers (database picks order arbitrarily) | consecutive |
RANK | same number | skips (adds the tie count) |
DENSE_RANK | same number | consecutive (no gap) |
See all three side by side
The best way to lock this in is to run them on a dataset that actually has ties:
When to reach for each
ROW_NUMBER — when you need exactly N rows, no exceptions. Deduplication (rn = 1 keeps one copy), pagination (page 2 = rn BETWEEN 11 AND 20), and top-N-per-group queries all need this guarantee.
RANK — when you want sports-style ranking: two gold medals, no silver. Leaderboards where tied competitors share a position and the next position is skipped to reflect that.
DENSE_RANK — “find the 3rd highest salary” problems. You want a true ordinal position — the 3rd distinct salary level — not the 3rd row. If two people share salary rank 1, you still want DENSE_RANK = 2 to be the second-highest salary, not skipped to 3.
-- "Find all employees with the 2nd highest salary"
-- RANK would work but might return 0 rows if rank 2 is skipped
-- DENSE_RANK is the safe choice:
SELECT name, salary
FROM (
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM employees
) ranked
WHERE dr = 2;