Top N per group
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — The best of each group
Time to put the pieces together. The strongest Codémon of each trainer is a classic question, and you already know one answer — a correlated subquery:
SELECT c.trainer_id, c.name, c.level
FROM codemon c
WHERE c.level = (SELECT MAX(level) FROM codemon WHERE trainer_id = c.trainer_id)
ORDER BY c.trainer_id;It works, but it is rigid. The window version numbers the Codémons inside each team, strongest first, then keeps position 1. A window cannot sit in WHERE, so the numbering goes in a CTE — the CTE, then filter pattern again:
WITH ranked AS (
SELECT name, trainer_id, level,
ROW_NUMBER() OVER (PARTITION BY trainer_id ORDER BY level DESC) AS rn
FROM codemon
WHERE trainer_id IS NOT NULL
)
SELECT t.name AS trainer, r.name AS codemon, r.level
FROM ranked r
INNER JOIN trainers t ON t.trainer_id = r.trainer_id
WHERE r.rn = 1
ORDER BY t.name;Same three leaders. The advantage of the window: change rn = 1 into rn <= 2 and you get the top two per team — the subquery would need a full rewrite.
The pattern
1) CTE: SELECT …, ROW_NUMBER() OVER (PARTITION BY group ORDER BY value DESC) AS rn. 2) Main query: WHERE rn = 1 (or rn <= N). It answers "latest per customer", "first order of each day", "top 3 per category" — any best-of-each-group question.
Exercise 2 — Top N, latest, first
The pattern bends in every direction. First, the top two per team — rn <= 2 instead of rn = 1:
WITH ranked AS (
SELECT name, trainer_id, level,
ROW_NUMBER() OVER (PARTITION BY trainer_id ORDER BY level DESC) AS rn
FROM codemon
WHERE trainer_id IS NOT NULL
)
SELECT trainer_id, name, level, rn
FROM ranked
WHERE rn <= 2
ORDER BY trainer_id, rn;Taylor's third Codémon, Firochu, is the only one left out. Order by a date instead of a level and you get "the most recent of each group" — every trainer's latest badge:
WITH ranked AS (
SELECT trainer_id, gym_id, earned_on,
ROW_NUMBER() OVER (PARTITION BY trainer_id ORDER BY earned_on DESC) AS rn
FROM badges
)
SELECT t.name, g.name AS gym, r.earned_on
FROM ranked r
INNER JOIN trainers t ON t.trainer_id = r.trainer_id
INNER JOIN gyms g ON g.gym_id = r.gym_id
WHERE r.rn = 1
ORDER BY t.name;And with an ascending ORDER BY, the first — each trainer's first battle, after stacking the two fighter columns with UNION ALL:
WITH fights AS (
SELECT trainer1_id AS trainer_id, battle_date FROM battles
UNION ALL
SELECT trainer2_id, battle_date FROM battles
),
ranked AS (
SELECT trainer_id, battle_date,
ROW_NUMBER() OVER (PARTITION BY trainer_id ORDER BY battle_date) AS rn
FROM fights
)
SELECT t.name, r.battle_date AS first_battle
FROM ranked r
INNER JOIN trainers t ON t.trainer_id = r.trainer_id
WHERE r.rn = 1
ORDER BY t.name;Ties at the top
ROW_NUMBER keeps exactly one row per group, even when two rows tie for first — which one is arbitrary unless the ORDER BY breaks the tie. To keep every tied winner, use RANK() instead and filter on rank = 1.
Exercise 3 — Why the CTE
Why is ROW_NUMBER() computed in a CTE before filtering on rn = 1?
Hint : WHERE runs before the windows are computed, so the number does not exist yet.
Exercise 4 — Weakest per team
Complete the query so that it returns the weakest Codémon of each trainer.
Hint : Weakest first, then keep only the first position.
Exercise 5 — Variations
In the CTE-then-filter pattern, match each variation with the rows it keeps.
ORDER BY level DESC … WHERE rn = 1ORDER BY level … WHERE rn = 1ORDER BY earned_on DESC … WHERE rn = 1ORDER BY level DESC … WHERE rn <= 3Exercise 6 — Other groups, other windows
The group does not have to be a trainer. The strongest Codémon of each type:
WITH ranked AS (
SELECT name, type, level,
ROW_NUMBER() OVER (PARTITION BY type ORDER BY level DESC) AS rn
FROM codemon
)
SELECT type, name, level
FROM ranked
WHERE rn = 1
ORDER BY type;Six types, six leaders: for fire it is Pyrochu (14) over Firochu (9), for plant Florachu (12) over Plantachu (8). The other four types have a single Codémon, who leads by default. To finish, window functions combine with everything else you know — joins included. A running count of wins per trainer, battle after battle:
SELECT b.battle_date, t.name AS winner,
COUNT(*) OVER (PARTITION BY b.winner_id ORDER BY b.battle_date) AS wins_so_far
FROM battles b
INNER JOIN trainers t ON t.trainer_id = b.winner_id
ORDER BY b.battle_date;Morgan reached 3 wins on March 10; Taylor's second win came on March 18; Alex stayed at 1. Partition, order, aggregate — the same three ingredients, from the first exercise to the last.
Exercise 7 — Keeping ties
Two Codémons of the same trainer share the top level. To keep both as leaders, which function do you number with before filtering on = 1?
RANK()ROW_NUMBER()NTILE(1)LAG()Hint : Only a ranking that gives tied rows the same number keeps them both.
Exercise 8 — The two latest badges
Professor Pi wants each trainer's two most recent badges.
Number each trainer's badges from the most recent (ROW_NUMBER() in a CTE ranked), keep the first two, and return name (the trainer), gym (the gym's name) and earned_on. Sort by name, then earned_on descending.
-- Write your query below