Ranking functions and top-N-per-group

Three ranking functions, one difference: ties

All three assign a position within an ordered partition. They agree on everything except what happens when two rows tie on the ORDER BY value:

• ROW_NUMBER() — a strict 1, 2, 3, 4… No ties allowed; if two rows are equal it still gives them different numbers (the order between them is arbitrary unless you add a tiebreaker column). Use it when you need a unique sequence: top-N, dedup, pagination.
• RANK() — ties get the same rank, then the next rank skips. Two rows tied for 1st are both 1, and the next row is 3 (2 is gone). This is “Olympic” ranking: two golds, no silver.
• DENSE_RANK() — ties get the same rank, but the next rank does not skip. Two 1s, then 2. Use it for “how many distinct levels are there,” e.g. distinct price tiers.

All three produce the same result when no ties exist — the distinction only matters at the boundary condition, which is exactly when your requirement is ambiguous. When you see a ranking function in a query, ask yourself: “what should happen if two rows share the top score?” — the answer picks the function.

SELECT region, salesperson, total,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY total DESC) AS rn,
       RANK()       OVER (PARTITION BY region ORDER BY total DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY region ORDER BY total DESC) AS drnk
FROM region_sales;

Top-N-per-group: the canonical pattern

You cannot filter on a window function in the same SELECT’s WHERE — window functions are computed after WHERE, in a later logical phase. So the pattern is always two levels: compute the rank in a subquery (or CTE), then filter the rank in the outer query.

SELECT region, salesperson, total
FROM (
  SELECT region, salesperson, total,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY total DESC) AS rn
  FROM region_sales
) ranked
WHERE rn <= 3
ORDER BY region, rn;

This is the answer to “top 3 per region,” “5 most recent orders per customer,” “the highest-paid employee in each department.” Choosing the ranking function matters here: ROW_NUMBER gives exactly N rows per group (ties broken arbitrarily); RANK may return more than N if there’s a tie at the boundary (three people tied for 3rd all have rank 3 and all pass rnk <= 3). For “exactly the top 3,” use ROW_NUMBER with a deterministic tiebreaker; for “everyone who reached the top-3 score,” use RANK.

Dedup: keep one row per key

The same machinery deduplicates. To collapse duplicate rows to the newest per key, rank by recency and keep rn = 1:

-- keep the most recent event per user, drop older duplicates
SELECT user_id, ts, payload
FROM (
  SELECT user_id, ts, payload,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ts DESC) AS rn
  FROM events
) e
WHERE rn = 1;

PARTITION BY user_id makes each user a group; ORDER BY ts DESC puts the newest first; rn = 1 is that newest row. This is cleaner and far faster than DISTINCT ON gymnastics or a self-join, and it generalizes: rn <= k keeps the k newest.

NTILE(n) is the bucketing cousin — it splits the ordered partition into n roughly equal buckets and labels each row 1…n, the basis for quartiles and percentile bands. PERCENT_RANK() and CUME_DIST() give the relative standing of a row as a fraction in [0, 1] — “this sale is in the 92nd percentile of its region.”