P10 · SQL & Querying
SQL RANK vs ROW_NUMBER — the ties trap
How SQL window functions rank rows — and the trap where ROW_NUMBER invents an order that tied scores don't actually have.
Start with the basics
A window function computes a value across a set of related rows — a rank, a running total, the previous row's value — without collapsing them the way GROUP BY does.
You write it with OVER(...): an ORDER BY inside the window ranks or sequences the rows, and a PARTITION BY restarts the calculation for each group.
So every row keeps its own line and gains a new column — its rank, its running sum, its neighbour. The traps are almost all about how ties are handled.
One that works
| name | score |
|---|---|
| Ana | 90 |
| Ben | 80 |
SELECT name, score,
RANK() OVER (ORDER BY score DESC) AS place
FROM scores
ORDER BY place;| name | score | place |
|---|---|---|
| Ana | 90 | 1 |
| Ben | 80 | 2 |
Each row keeps its own line and gains a rank — no rows are collapsed.
A window function computes across a set of rows related to the current row, without collapsing them into one.
When to use it
- Reach for a window function when you want a rank, a running total, or the previous/next row — while keeping every row.
- Use PARTITION BY to restart the calculation per group (per customer, per region).
- If you are collapsing to one row per group, that is GROUP BY; if you want to keep the rows, that is a window.
That is also where ranking quietly lies. When two rows tie, the ranking function you pick decides whether they share a place or get split apart — and one of them invents an order the data does not have. Here is the day that bites.
See it happen
The standings that split a tie — ROW_NUMBER vs RANK
Ana and Ben both scored 90. You want the standings. Watch how ROW_NUMBER and RANK treat that tie differently.
| name | score |
|---|---|
| Ana | 90 |
| Ben | 90 |
| Cy | 80 |
ROW_NUMBER — invents an order
SELECT name,
ROW_NUMBER() OVER (ORDER BY score DESC) AS place
FROM entries
ORDER BY place;| name | place |
|---|---|
| Ana | 1 |
| Ben | 2 |
| Cy | 3 |
RANK — ties share a place
SELECT name,
RANK() OVER (ORDER BY score DESC) AS place
FROM entries;| name | place |
|---|---|
| Ana | 1 |
| Ben | 1 |
| Cy | 3 |
Ana and Ben tied at 90 but the standings put Ben in second — ROW_NUMBER invented an order the scores don't have.
Walk the full example in the unit →The rest of the family
More ways a window function surprises you
A window PARTITION returns every row with the summary attached; GROUP BY returns one row per group — pick the wrong one and the shape is wrong.
A window result can't be used in WHERE — wrap it in a subquery (or use QUALIFY) to keep only the top N per group.
Looking at the previous or next row returns nothing at the start or end of each group; that edge blank is by design.
SELECT DISTINCT is applied after window functions compute, which can produce surprising duplicates or collapses.
Splitting rows into N buckets deals the leftover rows to the leading buckets, so the buckets sit on unequal populations.
Self-check
How to spot it in your own SQL
- Two rows that tie end up on different ranks.
- You tried to filter a ranking in WHERE and it errored.
- You wanted every row plus a summary, but got one row per group (or vice versa).
- A 'top N per group' query returns the wrong count per group.
What people miss
Window functions feel like magic until a tie shows up. The wrong ranking function silently invents an order, and the standings look authoritative while being wrong for exactly the rows that tied.
Go deeper
Ask Colearn
Instant answers, grounded in the same verified material the diagnostic grades against.
What's the difference between ROW_NUMBER, RANK, and DENSE_RANK?
They differ only on ties. ROW_NUMBER always gives distinct numbers (breaking ties arbitrarily); RANK gives ties the same number and then skips (1,1,3); DENSE_RANK gives ties the same number without skipping (1,1,2).
Pick the one that matches the sentence you'd say out loud — 'tied for first' is RANK or DENSE_RANK, not ROW_NUMBER.
from the unit →Why can't I filter my ROW_NUMBER in WHERE?
WHERE runs before window functions compute, so the rank doesn't exist yet at that point.
Wrap the query in a subquery and filter the rank in the outer query, or use QUALIFY where your database supports it.
from the unit →When should I use a window vs GROUP BY?
Use GROUP BY when you want one summarized row per group. Use a window function when you want to keep every row and attach a summary (a rank, a running total) to each.
PARTITION BY is the window's version of grouping — it restarts the calculation per group without collapsing the rows.
from the unit →Why is LAG returning blank for some rows?
The first row of each group has no previous row, so looking back returns nothing — that blank is expected at the edge.
Provide a default value for the edge, or handle the blank explicitly.
from the unit →That’s the free taste