SQL ROW_NUMBER with PARTITION BY
Use ROW_NUMBER and PARTITION BY to find the two best-selling albums in each genre.
Sample query
WITH album_sales AS (
SELECT a.id, a.genre, a.title, COALESCE(SUM(s.quantity), 0) AS copies_sold
FROM albums AS a
LEFT JOIN sales AS s ON s.album_id = a.id
GROUP BY a.id, a.genre, a.title
), ranked AS (
SELECT genre, title, copies_sold,
ROW_NUMBER() OVER (
PARTITION BY genre
ORDER BY copies_sold DESC, title, id
) AS position
FROM album_sales
)
SELECT genre, position, title, copies_sold
FROM ranked
WHERE position <= 2
ORDER BY genre, position;Rank albums after combining their sales
To pick two albums per genre for a featured display, first calculate one sales total per album. The album_sales query does that before any ranking takes place. Ranking the original sales rows would give albums with several transactions several chances to take a spot.
Restart the numbering for every genre
PARTITION BY genre tells ROW_NUMBER to start at one again when the genre changes. Inside each genre, sort by copies sold from highest to lowest. Use title and ID to break ties consistently. The outer query keeps positions one and two after those numbers have been assigned.
Check genres with fewer than two albums
Night Bus and Blue Hour take the two Jazz spots. Ambient has just one album, so Low Tide is the only Ambient row. Because the first query uses LEFT JOIN, it can include albums with no sales. If the display should feature only albums that sold at least one copy, filter copies_sold before assigning positions.
Query result
| genre | position | title | copies_sold |
|---|---|---|---|
| Ambient | 1 | Low Tide | 0 |
| Electronic | 1 | Soft Circuit | 0 |
| Indie | 1 | Static Bloom | 3 |
| Indie | 2 | Coastline | 1 |
| Jazz | 1 | Night Bus | 5 |
| Jazz | 2 | Blue Hour | 2 |
