Handling Ties in Top-N
When to use RANK or DENSE_RANK so tied rows are all included.
Handling Ties in Top-N is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
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)RANK Leaves Gaps
RANK gives tied rows the same rank, then skips the next values to account for the ties. Two rows tied at rank 2 are both 2, and the following row jumps to rank 4 (not 3).
Use RANK when you want "top 3 positions" and a position can hold multiple people, mimicking real competition standings where two silver medalists mean no bronze.
-- Salaries: 100, 90, 90, 80
-- RANK -> 1, 2, 2, 4 (rank 3 is skipped)
RANK() OVER (ORDER BY salary DESC)DENSE_RANK Has No Gaps
DENSE_RANK also gives tied rows the same rank but does not skip afterward. Two rows tied at 2 are both 2, and the next distinct value is 3.
Use DENSE_RANK when the question is about distinct values: "the 3 highest distinct salary levels" or "top 3 price tiers". It counts unique values, not rows.
-- Salaries: 100, 90, 90, 80
-- DENSE_RANK -> 1, 2, 2, 3 (no gap)
DENSE_RANK() OVER (ORDER BY salary DESC)Side-by-Side Comparison
For the salary list 100, 90, 90, 80 the three functions produce:
- ROW_NUMBER: 1, 2, 3, 4
- RANK: 1, 2, 2, 4
- DENSE_RANK: 1, 2, 2, 3
This table is worth memorizing cold. The interviewer may simply ask you to fill it in for a given list, and getting the gap behavior right is the whole point.
SELECT salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;Include All Tied Rows With RANK
To answer "top 3, but include everyone tied at the boundary", filter on RANK() <= 3. If two people share rank 3, both appear, so the result may contain more than 3 rows.
This is the intent behind questions phrased as "top 3 positions" rather than "any 3 rows". State explicitly that the row count can exceed N when ties occur.
WITH ranked AS (
SELECT name, department, salary,
RANK() OVER (
PARTITION BY department ORDER BY salary DESC
) AS rnk
FROM employees
)
SELECT name, department, salary, rnk
FROM ranked
WHERE rnk <= 3
ORDER BY department, rnk;Top-3 Distinct Levels With DENSE_RANK
When the requirement is the "top 3 distinct salary levels" and you want every employee at those three levels, use DENSE_RANK() <= 3.
This can return many rows: all employees in the top three pay grades, however crowded those grades are. The key insight is that DENSE_RANK counts distinct values, so the filter selects value-tiers rather than a fixed number of rows.
WITH ranked AS (
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees
)
SELECT name, salary, drnk
FROM ranked
WHERE drnk <= 3
ORDER BY drnk;Decision Guide
Map the wording to the function:
- "Exactly N rows" or pagination →
ROW_NUMBER(add a tiebreaker). - "Top N positions, ties share a position" →
RANK. - "Top N distinct values / levels / tiers" →
DENSE_RANK.
When the spec is ambiguous, ask: "If there is a tie at the cutoff, should I include all tied rows or cap at N?" That clarifying question itself earns points.
Tiebreakers Still Matter
Even with RANK or DENSE_RANK, adding a secondary ORDER BY key controls the display order of tied rows and keeps output stable. It does not change which rows qualify, but it makes results reproducible.
With ROW_NUMBER, the tiebreaker is essential because it determines which tied row survives the rn cutoff at all.
RANK() OVER (
PARTITION BY department
ORDER BY salary DESC, name ASC
) AS rnkWorked Example: Top Scorers
A game_scores table has player and score. The interviewer wants "all players in the top 2 score positions". A tie for first means two players at position 1, then the next distinct score is position 2.
"Positions" with no skipping after a tie points to DENSE_RANK. If they instead said "top 2 standings with skips", you would switch to RANK. Listen for the exact phrasing.
WITH r AS (
SELECT player, score,
DENSE_RANK() OVER (ORDER BY score DESC) AS pos
FROM game_scores
)
SELECT player, score, pos
FROM r
WHERE pos <= 2
ORDER BY pos, player;Common Mistakes to Avoid
Pitfalls interviewers watch for:
- Using
ROW_NUMBERwhen the question wants ties included, silently dropping a qualifying row. - Confusing
RANKandDENSE_RANKgap behavior. - Forgetting that
RANK/DENSE_RANKfilters can return more than N rows, then claiming the result is wrong. - Omitting
PARTITION BYwhen the top-N is per group, ranking the whole table instead.
Quick Check
Pick the right ranking function for the tie behavior described.
Recap: Handling Ties
Three functions, three tie behaviors:
- ROW_NUMBER: always unique, exactly N rows, ties broken arbitrarily.
- RANK: ties share a rank, then gaps appear.
- DENSE_RANK: ties share a rank, no gaps, counts distinct values.
Choose by the question's wording, ask a clarifying question when ambiguous, and remember RANK/DENSE_RANK filters may return more than N rows by design.
Frequently asked questions
Is the “Handling Ties in Top-N” lesson free?
Yes — the full text of “Handling Ties in Top-N” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “Handling Ties in Top-N”?
When to use RANK or DENSE_RANK so tied rows are all included. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Handling Ties in Top-N” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.