Top N per group
The three most expensive products per category, the last three orders per customer: ROW_NUMBER over a partition, then keep ranks up to N.
Last updated
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY price DESC
) AS rn
FROM products
)
WHERE rn <= 3;Why it’s written this way
GROUP BY can answer "the top price per category" but not "the top three ROWS" — aggregation collapses the rows you wanted to keep. A window function ranks rows without collapsing them: PARTITION BY restarts the numbering per category, ORDER BY decides what "top" means.
The wrapping subquery exists because WHERE runs before window functions are computed — rn doesn't exist yet at WHERE time, so you filter one level up.
Edge cases to know
- →Ties: ROW_NUMBER picks an arbitrary winner among equal prices. RANK() keeps ties (and can return more than N rows); pick deliberately.
- →DuckDB, Snowflake and BigQuery let you skip the subquery with QUALIFY rn <= 3 — neater, but not portable to Postgres or MySQL.
- →For large tables, an index (or sort key) matching the PARTITION BY / ORDER BY columns is what keeps this fast.
Part by part
- ROW_NUMBER() OVER (
- A window function. Unlike an aggregate it does not collapse rows — it adds a number to each one while leaving every row in place.
- PARTITION BY category
- Restarts the numbering for each category, which is what makes the result per-group rather than per-table. Without it the numbering runs once across everything.
- ORDER BY price DESC
- Decides which row in each group gets number 1. This ordering is internal to the window and is independent of any ORDER BY on the outer query.
- WHERE rn <= 3
- The filter, and the reason for the subquery: a window function cannot be referenced in the WHERE of the same SELECT that computes it.
Common mistakes
SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn FROM products WHERE rn <= 3;WHERE runs before window functions are evaluated, so rn does not exist yet and the database rejects the query. The subquery is not stylistic — it is what creates a stage where rn is a real column.
ORDER BY priceOmitting DESC numbers each group from cheapest upward, so the query returns the three lowest prices while reading exactly like a top-three query. It produces plausible output, which is what makes it dangerous.
RANK() OVER (PARTITION BY category ORDER BY price DESC)RANK gives tied rows the same number and then skips, so a three-way tie for first returns four or more rows for that group. Use it deliberately when ties should all be included; use ROW_NUMBER when you want exactly N.
Questions
What happens when two rows tie on price?
ROW_NUMBER breaks the tie arbitrarily and the choice can differ between runs. Add a second ORDER BY column — an id, or a timestamp — when you need the result to be reproducible.
When should I use RANK or DENSE_RANK instead?
RANK when tied rows should all qualify and the next rank should skip; DENSE_RANK when ties should qualify without skipping. ROW_NUMBER is the right choice only when you want exactly N rows regardless of ties.
Is this efficient on a large table?
It sorts within each partition, so it benefits enormously from an index on the partition and order columns together. Without one the database sorts the whole table, which is where a query like this becomes slow rather than merely large.
Does this work in MySQL?
In MySQL 8.0 and later, yes — window functions arrived in that release. On 5.7 and earlier you need the older correlated-subquery or self-join approach, which is considerably harder to read and usually slower.