ROW_NUMBER, RANK and DENSE_RANK
ROW_NUMBER, RANK and DENSE_RANK number rows within a window; the difference is entirely in how they treat ties.
ROW_NUMBER() | RANK() | DENSE_RANK() OVER ([PARTITION BY ...] ORDER BY expr [ASC|DESC], ...)All three number the rows of a window in its ORDER BY order, and all three take no arguments. They differ only in how they handle peers — rows the ordering cannot tell apart.
| score | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 90 | 1 | 1 | 1 |
| 80 | 2 | 2 | 2 |
| 80 | 3 | 2 | 2 |
| 70 | 4 | 4 | 3 |
ROW_NUMBER never repeats a value; RANK gives peers the same rank and then skips; DENSE_RANK gives peers the same rank and does not skip.
Top N per group
Number inside a partition, then filter in an outer query. Use ROW_NUMBER() <= 3 when you want exactly three rows and do not care which tied row is dropped, and RANK() <= 3 when every row tied for third place must be kept.
Ties and determinism
These three ignore the frame clause entirely; only PARTITION BY and ORDER BY affect them. If the ordering does not fully break ties, the numbering is arbitrary and can change between runs, which shows up as a flapping report or a paginated list that repeats a row. Append a unique column as the last sort key. Note that ORDER BY units_sold DESC, id is the shorthand for ORDER BY units_sold DESC, id ASC — ASC is the default and applies per expression, not to the whole list.
Example
-- The three best-selling products in each category.
WITH ranked AS (
SELECT
p.category_id,
p.id,
p.name,
p.units_sold,
ROW_NUMBER() OVER w AS rn, -- 1,2,3,4 - always distinct
RANK() OVER w AS rnk, -- 1,2,2,4 - peers share, next value skips
DENSE_RANK() OVER w AS drnk -- 1,2,2,3 - peers share, no gap
FROM products AS p
WHERE p.discontinued = 0
-- p.id last so equal units_sold still produce a stable, repeatable order.
WINDOW w AS (PARTITION BY p.category_id ORDER BY p.units_sold DESC, p.id)
)
SELECT category_id, id, name, units_sold, rn, rnk, drnk
FROM ranked
WHERE rn <= 3 -- exactly three rows per category
ORDER BY category_id, rn;When to use it
- Take the three best-selling products in each category.
- Delete duplicate rows, keeping the newest per key.
- Build a leaderboard where tied scores share a place.
- Number line items per invoice without storing a sequence column.
More examples
Deleting duplicates, keeping the newest per key
The standard deduplication shape. Run it as a SELECT first with the same join and WHERE to see exactly which ids would go, then swap SELECT for DELETE.
-- Keep the most recent row per (user_id, provider); delete the rest.
DELETE t
FROM oauth_tokens AS t
JOIN (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY user_id, provider
ORDER BY created_at DESC, id DESC) AS rn
FROM oauth_tokens
) AS d ON d.id = t.id
WHERE d.rn > 1; -- everything except the first row of each group
-- A derived table containing a window function is always materialized in
-- MySQL 8.4 (merging is disabled for it), so the numbering is computed
-- before any row is deleted.Keeping tied rows in a top-three list
Which one you want is a product decision, not a technical one. Public leaderboards and prize lists usually need RANK so tied competitors are both shown; a fixed-size UI slot usually needs ROW_NUMBER.
WITH r AS (
SELECT name, score,
ROW_NUMBER() OVER (ORDER BY score DESC, name) AS rn,
RANK() OVER (ORDER BY score DESC) AS rnk
FROM leaderboard
)
-- Exactly three rows; a tie for third is cut arbitrarily by the name sort.
SELECT name, score FROM r WHERE rn <= 3 ORDER BY rn;
-- Three places, but possibly four or more rows if third place is shared.
SELECT name, score, rnk FROM r WHERE rnk <= 3 ORDER BY rnk, name;Numbering per group for display
Useful when the stored table has no per-parent sequence column. Generating the line number at read time avoids having to renumber stored rows every time a line is inserted or removed.
-- Line numbers that restart for each invoice, in insertion order.
SELECT
li.invoice_id,
ROW_NUMBER() OVER (PARTITION BY li.invoice_id ORDER BY li.id) AS line_no,
li.sku,
li.qty,
li.line_cents,
-- DENSE_RANK over a non-unique column groups equal SKUs under one number.
DENSE_RANK() OVER (PARTITION BY li.invoice_id ORDER BY li.sku) AS sku_group
FROM invoice_lines AS li
ORDER BY li.invoice_id, line_no;
Discussion