RANK() vs DENSE_RANK() vs ROW_NUMBER() — when does the gap matter and how do you choose?

Q: RANK() vs DENSE_RANK() vs ROW_NUMBER() — when does the gap matter and how do you choose?

**Target Audience:** Developers who know what the three ranking functions return and now need to choose one for a real task (top N, Nth highest, dedup), plus candidates preparing for the follow-ups. **Difficulty:** Hard **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. SQL Server and PostgreSQL version facts are cited. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the **Verified** section quotes each example output in full. The definitions (1, 2, 3, 4 vs 1, 2, 2, 4 vs 1, 2, 2, 3) are covered on [What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?](/interview-question/what-is-the-difference-between-row-number-rank-and-dense-rank). This page is about **choosing**: the situations where the gap changes whic

The problem

Question presented to candidate: "Say we pay a bonus to the top 3 sales reps in each region, and some reps have exactly the same revenue. Would you use ROW_NUMBER, RANK or DENSE_RANK to pick them? When does the difference actually change the result, and how do you decide?"

What a strong answer should cover:

The functions only differ on ties, so the choice is a business question: should ties share a place, and should "top N" mean N rows, N places or N distinct values?
WHERE rn <= N with ROW_NUMBER always returns at most N rows per group but drops tied rows arbitrarily unless there is a business tiebreaker; RANK <= N returns every row tied at the boundary (can be more than N); DENSE_RANK <= N returns N distinct values (can be many more rows)
The gap matters for equality filters: RANK() = 2 returns nothing after a tie for first, while DENSE_RANK() = 2 means "the second-highest value" (the Nth highest salary pattern)
ROW_NUMBER with a unique tiebreaker is the tool for dedup and "latest row per key"; RANK = 1 there can return two rows
Equivalents and performance: FETCH FIRST n ROWS WITH TIES (PostgreSQL 13+) behaves like RANK <= n for one group; PostgreSQL 15+ can stop a WindowAgg early on these filters

Clarifying questions expected:

If two reps tie at the cut-off, do both get the bonus, or do we need exactly three people?
Is there a natural tiebreaker (earlier close date, lower id), or would picking one be arbitrary?
Does "top 3" mean three people or the three highest revenue figures?

Code / implementation expected: Yes — the same "top 3" filter with all three functions, showing the different row counts.

Schema used by the examples:

window-functions
ranking
dense-rank

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who know what the three ranking functions return and now need to choose one for a real task (top N, Nth highest, dedup), plus candidates preparing for the follow-ups. Difficulty: Hard Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. SQL Server and PostgreSQL version facts are cited.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result shown was produced by actually running it; the Verified section quotes each example output in full.

The definitions (1, 2, 3, 4 vs 1, 2, 2, 4 vs 1, 2, 2, 3) are covered on [What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?](/interview-question/what-is-the-difference-between-row-num

The solution is hidden so you can try first

6 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLTop 3 in North with each function: 3, 4 or 5 reps qualify

SQLWhere the gap bites: RANK = 2 finds nobody in South, DENSE_RANK = 2 finds the second-highest revenue

SQLLatest plan per user: RANK = 1 returns two rows for a tied timestamp, ROW_NUMBER with a tiebreaker returns one

PostgreSQL: FETCH FIRST 2 ROWS WITH TIES keeps everyone tied with the last row

SELECT rep, revenue
FROM sales_reps
WHERE region = 'North'
ORDER BY revenue DESC
FETCH FIRST 2 ROWS WITH TIES;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.