Handling Ties in Top-N
When to use RANK or DENSE_RANK so tied rows are all included.
The Tie Follow-Up Question
Once you nail top-N with ROW_NUMBER, the interviewer twists the knife: "What if two employees have the exact same salary at the cutoff? Should both be included?"
This separates candidates who memorized one query from those who understand ranking semantics. The answer hinges on choosing between ROW_NUMBER, RANK, and DENSE_RANK. This lesson maps each function to the business intent.
ROW_NUMBER Hides Ties
ROW_NUMBER assigns a strictly unique integer per row. Even when two rows tie on the ordering column, one gets 2 and the other gets 3 in some arbitrary order.
Consequence: a WHERE rn <= 3 filter returns exactly 3 rows and may silently drop a tied row that arguably deserved to be included. That is fine when you genuinely want a fixed count, but wrong when ties should all qualify.
-- Salaries: 100, 90, 90, 80
-- ROW_NUMBER -> 1, 2, 3, 4 (the two 90s get 2 and 3 arbitrarily)
ROW_NUMBER() OVER (ORDER BY salary DESC)