VocalHyre
Sign up

SQL · Queries that survive scale

What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?

Mediumrankingwindow functionstiesUpdated Oct 2026
Quiz this track

Short answer

ROW_NUMBER gives every row a unique sequence number, RANK leaves gaps after ties, and DENSE_RANK does not. For scores 10, 10, 8 they return 1, 2, 3; 1, 1, 3; and 1, 1, 2.

All three are window functions that depend on an ORDER BY. The difference only appears when rows tie on the ordering value, so choose based on whether gaps and duplicate ranks are meaningful for the business rule.

sql
SELECT
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_number,
  RANK() OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM results;

Choosing the right one

  • ROW_NUMBER: pick exactly one row per group, such as the latest order per customer
  • RANK: competition-style ranking where a tie consumes the following positions
  • DENSE_RANK: top-N distinct values where gaps are unwanted
  • Add a deterministic tie-breaker to ROW_NUMBER so results are stable

ROW_NUMBER is often used with a CTE to deduplicate rows: partition by the duplicate key, order by the preferred record, and keep row number one. Without a unique tie-breaker, the chosen row can change between runs.

Do not use RANK for pagination or deduplication. Ties produce multiple rows with the same rank, which can return more rows than expected.

Follow-ups you should expect

Interviewers use these to check whether the first answer was understanding or memorisation.

  1. 1Which function would you use to keep the latest row per customer?
  2. 2Why is a tie-breaker important with ROW_NUMBER?
  3. 3How do these functions behave with NULL values in ORDER BY?
  4. 4Can you use these functions without PARTITION BY?

Say it in the interview

ROW_NUMBER assigns a unique sequence, so ties still get different numbers. RANK gives tied rows the same rank and leaves a gap, while DENSE_RANK gives tied rows the same rank without a gap. I use ROW_NUMBER with a deterministic tie-breaker for deduplication and latest-row queries. I use RANK or DENSE_RANK when the business meaning of ties matters.

Use this as a model, not a script. Saying it in your own words is what makes it survive follow-up pressure.

Related questions

MediumWhat are window functions and when should you use them?MediumWhat is a CTE and why would you use one?EasyHow does GROUP BY work?

Reading is step one

Now say it out loud.

Understanding an answer and delivering it under pressure are different skills. Rehearse this topic in a live SQL mock interview and get scored feedback on depth, clarity and confidence.

Start a free SQL interviewBrowse all questions