Ranking Functions

## What a ranking function computes A ranking function assigns a position to each row inside a *window* — the row set described by `OVER (PARTITION BY ... ORDER BY ...)`. `PARTITION BY` splits rows into independent groups and the numbering restarts at 1 in every group; the window `ORDER BY` decides the sequence in which positions are handed out. Nothing is collapsed: every input row survives and simply gains one extra column. Two rows are **peers** when the window `ORDER BY` cannot tell them apart — all the ordering expressions compare equal. Ties are exactly where the three functions part company. ## The three definitions - `ROW_NUMBER()` — a plain counter over the ordered partition: 1, 2, 3, … N. Values are always distinct, so peers are forced into some order and given different numbers. - `RANK()` — one plus the number of rows that strictly precede the current row's peer group. All peers share a number, and the next distinct value resumes at its own ordinal position, which produces gaps. - `DENSE_RANK()` — the count of distinct ordering values at or before the current row. All peers share a number, and the next distinct value is simply the next integer, so there are no gaps. ## Worked example ```sql SELECT player, points, ROW_NUMBER() OVER (ORDER BY points DESC) AS rn, RANK() OVER (ORDER BY points DESC) AS rnk, DENSE_RANK() OVER (ORDER BY points DESC) AS dns FROM scores; ``` With rows `ana 100`, `bo 90`, `cy 90`, `di 80` the output is: - ana: rn 1, rnk 1, dns 1 - bo: rn 2, rnk 2, dns 2 - cy: rn 3, rnk 2, dns 2 - di: rn 4, rnk 4, dns 3 Note two things. First, `bo` and `cy` are peers, so `RANK()` and `DENSE_RANK()` treat them identically while `ROW_NUMBER()` had to pick an order — whether `bo` gets 2 and `cy` gets 3 or the other way round is not determined by the query. Second, `di` shows the gap: `RANK()` says 4 because three rows precede it, `DENSE_RANK()` says 3 because it is the third distinct score. The gap widens with the size of the tie. If three players all scored 100, `RANK()` gives them 1, 1, 1 and the next distinct score gets 4; `DENSE_RANK()` gives 1, 1, 1 and then 2. ## Why gaps are a feature, not a bug The two behaviours answer different questions. `RANK()` answers "how many competitors are ahead of me, plus one?" — the classic medal-table rule where two silver medals mean nobody gets bronze. `DENSE_RANK()` answers "how many distinct levels exist at or above mine?" — the right shape when you are labelling salary bands, price tiers or grade levels, because you want consecutive labels regardless of how many people sit on each level. Neither is more correct; picking the wrong one silently changes what a report means. ## Choosing between them - Need exactly one row per position — one row to keep, one row per page slot, a strict 1..N sequence? `ROW_NUMBER()`. - Need standings where ties genuinely share a place and the following place absorbs the gap? `RANK()`. - Need consecutive tier labels, or the number of distinct values encountered so far? `DENSE_RANK()`. ## Determinism footnote `RANK()` and `DENSE_RANK()` are stable under ties by construction: peers always receive the same number, so re-running the query cannot shuffle their values. `ROW_NUMBER()` has to break every tie somehow, and the standard does not say how — the assignment among peers is implementation-defined and can change between runs, plans, or engine versions. If which specific row receives 1 matters, extend the window `ORDER BY` with a column combination that is unique, such as the primary key. ## Common traps Candidates often say the three are interchangeable, or that `ROW_NUMBER()` repeats numbers for equal values. Others treat the missing 3 in a `RANK()` result as an engine defect. A third trap is forgetting that with `PARTITION BY` every group starts again at 1, so the same number appears many times across the result — that is partitioning, not tie behaviour.