Ranking: ROW_NUMBER, RANK and DENSE_RANK

ROW_NUMBER numbers the rows 1, 2, 3, 4 with no ties. RANK gives ties the same number and then skips (1, 2, 2, 4). DENSE_RANK gives ties the same number without skipping (1, 2, 2, 3).

Why it matters

"Top 3 per department", "the second-highest salary", "each customer's first order" — ranking questions are the most common window-function questions in interviews. The only hard part is ties, and the three functions differ exactly there.

The idea

All three need ORDER BY inside OVER, which decides who comes first.
ROW_NUMBER always gives different numbers, so if two rows tie, which one gets 3 and which gets 4 is arbitrary unless you add a tiebreaker column.
RANK is like a sports ranking: two people sharing 3rd place means nobody is 4th.
DENSE_RANK doesn't leave gaps, so "the 2nd highest salary" is simply DENSE_RANK = 2.

Add PARTITION BY to restart the numbering in each group.

A race with two runners crossing the line together in third. ROW_NUMBER is the finish-line camera that must put one of them first. RANK is the medal table: both get third, and the next runner is fifth. DENSE_RANK is "positions": both third, the next runner fourth.

A photo finish has a real answer. ROW_NUMBER's choice between tied rows doesn't — it can differ between runs — so always add a tiebreaker (like name or id) when you use ROW_NUMBER.

Syntax

ROW_NUMBER() OVER (PARTITION BY g ORDER BY x DESC, tiebreaker)
RANK()       OVER (PARTITION BY g ORDER BY x DESC)
DENSE_RANK() OVER (PARTITION BY g ORDER BY x DESC)

How it works

  1. Sort by salary, highest first: Asha 95000, Hana 80000, then Ben and Chen on 70000, then Dara 60000.
  2. ROW_NUMBER: 1, 2, 3, 4, 5 — Ben and Chen get different numbers; the tiebreaker (name) puts Ben first.
  3. RANK: 1, 2, 3, 3, 5 — they share 3, and 4 is skipped.
  4. DENSE_RANK: 1, 2, 3, 3, 4 — they share 3, and Dara is 4.
  5. To keep only the top rows, compute the rank in a CTE and filter with WHERE outside it.

Key points

Common mistakes

Not true: RANK is the function to use for finding the second highest salary.
In fact: After a tie for first place, RANK jumps from 1 to 3, so there is no rank 2. DENSE_RANK leaves no gaps: use it for "Nth highest".

In the interview

All three number rows in the order given by the OVER clause's ORDER BY, optionally restarting per PARTITION BY. ROW_NUMBER gives unique sequential numbers, so I add a deterministic tiebreaker; RANK gives tied rows the same rank and leaves gaps; DENSE_RANK gives the same rank without gaps. For "Nth highest distinct value" I use DENSE_RANK, and for "exactly one row per group" I use ROW_NUMBER — always filtering in an outer query.

Part of Unit 5: Window functions: ranking, running totals and comparing rows in the free SQL course.