AI Hints
Homeโ€บSQL Tutorialโ€บWindow Functions
SQL ยท Lesson 65 of 126

Top N per Group

The best in each category.

โฑ 2 min readโ–ถ Video at 1:32:34๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Top N per Group
From the video: Top N per Group
  • 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   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.

โœ๏ธ Exercise: The cheapest in each genre

The cheapest book in each genre

Show hint

Same pattern, ORDER BY price (ascending)

Show solution
solution.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   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
Next up: Running Totals โ€” adding up as you go