Top-N per group
The pattern in every analytics interview — top 3 products per category, latest order per user, best score per team — and the wrap-and-filter idiom behind it.
What you'll learn
- Why WHERE ROW_NUMBER() <= N doesn't work directly
- The wrap-and-filter idiom: window in the inner query, filter in the outer
- Variations — latest-per-group, best-per-group, first-N-per-group
- When Postgres's DISTINCT ON beats it
Before you start
If you have interviewed for an analytics or data role, you have met this question. Top 3 products per category. Latest order per user. Best score per player. Same shape, different nouns — and it is probably the single most useful SQL pattern you will learn this week.
Why you cannot filter directly
Partition, rank inside the partition, then keep rank 1.
The instinctive attempt fails:
SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products
WHERE rn <= 3; -- ERROR: rn doesn't exist yetA window function is evaluated after the WHERE of its own SELECT, so when WHERE runs, rn has not been computed. The fix is always the same — compute the window in an inner query, then filter in the outer one:
WITH ranked AS (
SELECT category, name, price,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC, name) AS rn
FROM products
)
SELECT category, name, price FROM ranked WHERE rn = 1;On our product catalogue — two Stationery, one Grocery, two Electronics — that returns the dearest item in each category:
| category | name | price |
|---|---|---|
| Electronics | Laptop | 900 |
| Grocery | Coffee | 9 |
| Stationery | Notebook | 6 |
PARTITION BY category restarts the numbering per category, so rn = 1 is each category’s top row. Notice the tiebreaker ORDER BY price DESC, name — without one, “the top row” could shift between runs whenever two rows tie. Always add a tiebreaker.
One pattern, many questions
The skeleton never changes; only the PARTITION BY and ORDER BY do. Swap them and you answer a whole family of questions:
- Latest per group —
PARTITION BY user_id ORDER BY order_date DESC, keeprn = 1. This is the foundation of every “current state” query: each user’s latest subscription, each device’s most recent ping. - First-N per group — order ascending, keep
rn <= N. First five visits, first three purchases. - Bottom-N — flip the
ORDER BYdirection. Worst performers, slowest queries.
And the choice of ranking function matters on ties: ROW_NUMBER keeps exactly one row per group (arbitrary on a tie), while RANK lets genuinely-tied rows share the top spot — a leaderboard where two players really are joint first wants RANK, a dedup that must keep one row wants ROW_NUMBER.
Practice
Quick check
Practice this in an interview
All questionsAssign ROW_NUMBER() (or DENSE_RANK() if ties should be included) partitioned by department and ordered by salary descending, then filter in an outer query or CTE where the rank is 3 or less. You cannot filter on a window function directly in WHERE — it must be wrapped.
Rank salaries with DENSE_RANK() ordered descending and keep rank 2 — it handles duplicate salaries and generalises to the Nth-highest. A correlated subquery (the max salary strictly below the overall max) also works, while LIMIT 1 OFFSET 1 is only safe when no two people share a salary.
Use DENSE_RANK() to assign rank without gaps — the Nth distinct salary value is the row where DENSE_RANK equals N. RANK() would produce wrong results when ties occur, and a plain ORDER BY LIMIT/OFFSET approach ignores ties entirely.
All three are extensions to GROUP BY that produce multiple levels of aggregation in a single query. ROLLUP produces hierarchical subtotals, CUBE produces all possible subtotal combinations, and GROUPING SETS lets you specify exactly which grouping combinations you want.