SQLIntermediate
ROW_NUMBER vs RANK vs DENSE_RANK
“Explain the difference and how each affects a "top 3" query with ties.”
What this tests
- Window function semantics
- Handling ties
Answers by level
Read the beginner answer first and notice what is missing.
ROW_NUMBER assigns 1, 2, 3 with no ties. RANK gives tied rows the same number and then skips (1, 2, 2, 4). DENSE_RANK gives ties the same number and does not skip (1, 2, 2, 3).
For "top 3" with ties: ROW_NUMBER ≤ 3 cuts a tie arbitrarily and returns exactly 3; RANK ≤ 3 can return 4 (two tied at 2, then rank 4 excluded); DENSE_RANK ≤ 3 can return many. Choose by whether ties should share a place.
Green flags · Red flags
Strong green flag · Explains how "top 3" returns different counts for each function.
Green flags
- Precise on skip vs no-skip
- Knows you filter windows in an outer query
- Adds a tiebreaker for determinism
Red flags
- Conflates the three
- Tries WHERE row_number() <= 3 in the same query
Follow-up questions
F1
Write "3 highest-paid employees per department".
Scenario
A "top 10 scores" leaderboard shows 12 rows some days. Which function is in use and is that a bug?