Learn SQL: Ranking with RANK and DENSE_RANK
Same time
In the hilly stage to Galmaarden the riders crossed the line in small groups. Whoever finishes together gets the same time. "Then they share their place in the time ranking too," says Griet. `ROW_NUMBER` knows no shared places. Time for something better.
Shared places
RANK() works like ROW_NUMBER(), but rows with the same value get the same number. After that it skips places: after three riders in 2nd place comes 5th place.
Here is the time behind in stage 3, in seconds. The WHERE runs first, so only the riders of that stage get ranked.
SELECT r.name, res.time_behind, RANK() OVER (ORDER BY res.time_behind) AS rnk FROM results res JOIN riders r ON r.id = res.rider_id WHERE res.stage_id = 3| name | time_behind | rnk |
|---|---|---|
| Wout De Clercq | 0 | 1 |
| Tibo Lauwers | 42 | 2 |
| Jesper Kuipers | 42 | 2 |
| Louis Garnier | 42 | 2 |
| Arne Van Hecke | 94 | 5 |
| Kasper Holm | 94 | 5 |
| Ruben de Graaf | 94 | 5 |
| Jens Moreels | 162 | 8 |
Three ways of counting
Side by side, for stage 4. ROW_NUMBER always gives different numbers, even for equal times: who goes first is then SQLite's own choice. RANK shares places and skips afterwards. DENSE_RANK shares too, but skips nothing: after three 2s comes 3.
SELECT rider_id, time_behind,
ROW_NUMBER() OVER (ORDER BY time_behind) AS row_num,
RANK() OVER (ORDER BY time_behind) AS rnk,
DENSE_RANK() OVER (ORDER BY time_behind) AS dense
FROM results WHERE stage_id = 4| rider_id | time_behind | row_num | rnk | dense |
|---|---|---|---|---|
| 11 | 0 | 1 | 1 | 1 |
| 2 | 18 | 2 | 2 | 2 |
| 10 | 38 | 3 | 3 | 3 |
| 13 | 38 | 4 | 3 | 3 |
| 9 | 65 | 5 | 5 | 4 |
| 3 | 73 | 6 | 6 | 5 |
| 8 | 73 | 7 | 6 | 5 |
| 12 | 73 | 8 | 6 | 5 |
Ranking after GROUP BY
Window functions run after GROUP BY. That is why you can rank on a total: RANK() OVER (ORDER BY SUM(...) DESC).
Here is the team classification on points: first add up per team, then rank.
SELECT t.name AS team, SUM(res.points) AS points,
RANK() OVER (ORDER BY SUM(res.points) DESC) AS place
FROM results res
JOIN riders r ON r.id = res.rider_id
JOIN teams t ON t.id = r.team_id
GROUP BY t.id, t.name| team | points | place |
|---|---|---|
| Polderwind | 94 | 1 |
| Équipe Nord | 72 | 2 |
| Flandrien Pro | 57 | 3 |
| Pajot Cycling | 41 | 4 |
Try it yourself
Build the riders' points classification. Show the name, the total points and the place. Riders with equal points share the place, and the next place is skipped.
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