Learn SQL: Windows per group with PARTITION BY
Per team, per stage
Every team wants its own little list: who is our youngest, who is our best rider per stage? "Four teams, five stages. That's a lot of lists," sighs Griet. Or one query that starts counting again for each group.
Starting again per group
With PARTITION BY inside the OVER you split the rows into groups. The window function starts again in each group: here the youngest rider of every team gets number 1.
Unlike with GROUP BY, all rows stay. So you don't get four rows but sixteen, each with a number within its own team.
SELECT r.name, t.name AS team, r.birth_date, ROW_NUMBER() OVER (PARTITION BY r.team_id ORDER BY r.birth_date DESC) AS num FROM riders r JOIN teams t ON t.id = r.team_id| name | team | birth_date | num |
|---|---|---|---|
| Milan Dhondt | Flandrien Pro | 2003-01-30 | 1 |
| Wout De Clercq | Flandrien Pro | 2001-07-02 | 2 |
| Lars Vermeulen | Flandrien Pro | 1999-03-14 | 3 |
| Jens Moreels | Flandrien Pro | 1997-11-23 | 4 |
| Tibo Lauwers | Pajot Cycling | 2002-09-17 | 1 |
| Arne Van Hecke | Pajot Cycling | 2000-05-09 | 2 |
| Senne Pauwels | Pajot Cycling | 1998-12-05 | 3 |
| Kasper Holm | Pajot Cycling | 1996-04-21 | 4 |
The best per group
A classic: the best row per group. Number per group first in a WITH, then keep only number 1.
PARTITION BY can take several columns too. Here you see, per stage, the best placed rider of each team: five stages, four teams, twenty rows.
WITH ranked AS (
SELECT res.stage_id, r.team_id, r.name, res.position,
ROW_NUMBER() OVER (PARTITION BY res.stage_id, r.team_id ORDER BY res.position) AS num
FROM results res JOIN riders r ON r.id = res.rider_id
)
SELECT stage_id, team_id, name, position FROM ranked WHERE num = 1| stage_id | team_id | name | position |
|---|---|---|---|
| 1 | 1 | Wout De Clercq | 10 |
| 1 | 2 | Arne Van Hecke | 6 |
| 1 | 3 | Ruben de Graaf | 2 |
| 1 | 4 | Louis Garnier | 1 |
| 2 | 1 | Jens Moreels | 3 |
| 2 | 2 | Tibo Lauwers | 5 |
| 2 | 3 | Jesper Kuipers | 1 |
| 2 | 4 | Hugo Lefèvre | 4 |
Try it yourself
Number the riders per team, the oldest gets 1. Show the name, the team_id and the number.
Ready for more?
The full lesson has 5 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson