Top N per Group
The best in each category.
Here's one of the most common interview questions in SQL. Find the top item in each category. The most expensive book in each genre. The best customer in each country. Window functions make it easy.

- A classic interview question
- The top item per category
- Window functions make it easy
The pattern
Two steps. First, a CTE numbers the books inside each genre, from most expensive down. Then the main query keeps only row number one. Run it. The most expensive book in every genre, one row each. To get the top three, you'd just write r n less than or equal to three.
WITH ranked AS (
SELECT genre, title, price,
ROW_NUMBER() OVER (PARTITION BY genre
ORDER BY price DESC) AS rn
FROM books
)
SELECT genre, title, price
FROM ranked
WHERE rn = 1;โโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโ โ genre โ title โ price โ โโโโโโโโโโโโโโผโโโโโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโค โ Business โ The Pricing Game โ 23.99 โ โ Cooking โ Spice Roads โ 27.99 โ โ Fantasy โ The Glass Forest โ 18.49 โ โ History โ Empires of Salt โ 22.99 โ โ Mystery โ The Silent Orchard โ 14.99 โ โ Romance โ Wild Coast โ 11.49 โ โ Self-help โ Running on Empty โ 15.99 โ โ Technology โ Python for Curious Minds โ 34.99 โ โ Thriller โ Night Train to Oslo โ 14.99 โ โโโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโ
The cheapest in each genre
Your turn. Find the cheapest book in each genre. Pause and try. Here's a solution. The same pattern, but order by price ascending, so the cheapest gets number one.
The cheapest book in each genre
Show hint
Same pattern, ORDER BY price (ascending)
Show solution
WITH ranked AS (
SELECT genre, title, price,
ROW_NUMBER() OVER (PARTITION BY genre
ORDER BY price) AS rn
FROM books
)
SELECT genre, title, price
FROM ranked
WHERE rn = 1;โโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโ โ genre โ title โ price โ โโโโโโโโโโโโโโผโโโโโโโโโโโโโโโโโโโโโโโโโโโผโโโโโโโโค โ Business โ Startup Stories โ 19.99 โ โ Cooking โ One Pan Dinners โ 21.49 โ โ Fantasy โ Crown of Ash โ 16.99 โ โ History โ The Map Maker's Daughter โ 18.99 โ โ Mystery โ Murder at Mill Lane โ 10.99 โ โ Romance โ Garden of Small Things โ 8.99 โ โ Self-help โ Five Minute Habits โ 12.99 โ โ Technology โ SQL in a Weekend โ 29.99 โ โ Thriller โ Moonlight Express โ 11.99 โ โโโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโโโโโโโดโโโโโโโโ
Summary
Number inside each group with partition by, then keep the top rows. That pattern answers top N per group, every time. Next, running totals.
Key takeaways- Number within each group
- Keep rn = 1, or rn <= N
- An interview favourite