Learn SQL: Window functions and ROW_NUMBER
A number for every row
"On the podium I want the youngest riders in the spotlight," says Griet. For that you need to number them, from young to old. `GROUP BY` can't do it: the riders would disappear into a little group.
A window over the rows
A window function calculates something over a set of rows, the window, but leaves every row standing. That is the big difference from GROUP BY.
ROW_NUMBER() gives every row a sequence number. After OVER you say in brackets in which order to number: here from old to young.
SELECT name, birth_date, ROW_NUMBER() OVER (ORDER BY birth_date) AS num FROM riders| name | birth_date | num |
|---|---|---|
| Kasper Holm | 1996-04-21 | 1 |
| Jesper Kuipers | 1997-01-19 | 2 |
| Jens Moreels | 1997-11-23 | 3 |
| Louis Garnier | 1998-07-15 | 4 |
| Senne Pauwels | 1998-12-05 | 5 |
| Lars Vermeulen | 1999-03-14 | 6 |
| Thijs Bakker | 1999-08-28 | 7 |
| Arne Van Hecke | 2000-05-09 | 8 |
The total next to every row
SUM, COUNT and AVG can be window functions too. With an empty OVER () the window is the whole table: every row gets the total next to it, and not a single row disappears.
That lets you compare each row with the whole, something GROUP BY can't do.
SELECT finish, km, SUM(km) OVER () AS total FROM stages| finish | km | total |
|---|---|---|
| Lennik | 164 | 701 |
| Lennik | 18 | 701 |
| Galmaarden | 178 | 701 |
| Pepingen | 185 | 701 |
| Halle | 156 | 701 |
Filtering on the number
WHERE runs before the window functions: at that moment the number doesn't exist yet. That is why a window function is not allowed in WHERE.
The way out: number first in a WITH or a subquery, then filter on the column. That's how you find the three youngest riders.
WITH numbered AS (
SELECT name, birth_date, ROW_NUMBER() OVER (ORDER BY birth_date DESC) AS num
FROM riders
)
SELECT name, num FROM numbered WHERE num <= 3| name | num |
|---|---|
| Ruben de Graaf | 1 |
| Ben Schmit | 2 |
| Milan Dhondt | 3 |
Try it yourself
Show the finish town and the distance of every stage, with a sequence number from the longest (1) to the shortest.
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson