RecipesTop N per group
Top N per group
Return the top N rows within each group — highest-paid employee per department, latest order per customer, best-selling product per category.
The problem
You have rows in groups and you want the top few from each group, not the top
few overall. LIMIT alone can't do it — it caps the whole result.
The pattern
Number the rows within each group with ROW_NUMBER(), then keep the low numbers.
SELECT *
FROM (
SELECT
e.*,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC, name
) AS rn
FROM employees e
) ranked
WHERE rn <= 2;SQL playground
Loading editor…
⌘/Ctrl + Enter
The subquery (or a CTE) is required: you can't put a window function directly in
WHERE, because WHERE runs before window functions.
Which numbering function?
- ROW_NUMBER — exactly N rows per group; ties broken by your tiebreaker. Use this for "top 3 rows".
- RANK — ties all get in, so "top 3" can return 4+ rows. Use this for "everyone in the top 3 salaries".
- DENSE_RANK — "top 3 distinct values", ties share a slot, no gaps.
Latest row per key (N = 1)
The most common case — most recent order per customer:
SQL playground
Loading editor…
⌘/Ctrl + Enter
Gotchas
- Non-deterministic ties. Without a unique tiebreaker in
ORDER BY, which row getsrn = 1can change between runs. Add a primary key as the last sort key. - ** Engines with
QUALIFY** (BigQuery, Snowflake, DuckDB) let you skip the subquery:... QUALIFY ROW_NUMBER() OVER (...) <= 2. - Big groups. This scans and sorts every row. If you only need N = 1 and have
an index on
(group_key, sort_key), a lateral/correlated subquery can be faster.
Was this page helpful?