PatternSQL
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
Pattern
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.