Top N per group

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 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?

Options
• Because a window function cannot be used in WHERE
• Because CTEs make window functions faster
• Because ROW_NUMBER only works inside WITH
• Because WHERE cannot compare integers

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.

Items to match
• ORDER BY level DESC … WHERE rn = 1
• ORDER BY level … WHERE rn = 1
• ORDER BY earned_on DESC … WHERE rn = 1
• ORDER BY level DESC … WHERE rn <= 3
Definitions
• The strongest of each group
• The weakest of each group
• The most recent of each group
• The top three of each group

Exercise 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?

Options
• 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.

Starter code
-- Write your query below
Back to the course