quiz vs the machine

Silver1120

Databases

The RANK DENSE RANK and ROW NUMBER

Three ranking functions that differ in how they handle ties and gaps.

4 min read · intro · beat Silver to climb

Three ways to number rows

SQL offers three ranking window functions that all assign positions within an ordered partition, but they treat ties differently.

ROW NUMBER gives every row a unique sequential number, even when ordering values are equal. Ties are broken arbitrarily unless you add more ORDER BY columns.
RANK gives tied rows the same rank, then skips numbers so the next distinct value jumps ahead.
DENSE RANK gives tied rows the same rank but does not skip, so ranks stay consecutive.

A worked example

Order scores 90, 90, 80. ROW NUMBER yields 1, 2, 3. RANK yields 1, 1, 3 because it leaves a gap after the tie. DENSE RANK yields 1, 1, 2 with no gap.

Choosing the right one

Use ROW NUMBER for pagination or picking exactly one row per group.
Use RANK when gaps after ties are meaningful, like leaderboard standings.
Use DENSE RANK when you want compact tiers without holes.

Key idea

ROW NUMBER is always unique, RANK shares ranks but leaves gaps after ties, and DENSE RANK shares ranks without gaps.

Check yourself

Answer to earn rating on the learn ladder.

1. For scores 90, 90, 80 ordered descending, what does DENSE RANK return?

2. Which function guarantees a unique number per row?

3. What distinguishes RANK from DENSE RANK?