ROW_NUMBER, RANK and DENSE_RANK
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Numbering rows
Averages and totals are not the only thing a window can produce. Give it an ORDER BY and it can number the rows: ROW_NUMBER() gives each row its position, strongest Codémon first:
SELECT ROW_NUMBER() OVER (ORDER BY level DESC) AS pos, name, level
FROM codemon;Aquachu is 1, Pyrochu 2, down to Plantachu at 8. The ORDER BY inside the window decides the numbering; the query's own ORDER BY decides the display — two different things, and you will often write both.
Add PARTITION BY and the numbering restarts in each group:
SELECT trainer_id, name, level,
ROW_NUMBER() OVER (PARTITION BY trainer_id ORDER BY level DESC) AS pos_in_team
FROM codemon
ORDER BY trainer_id, pos_in_team;Position 1 in each team is the strongest Codémon of that trainer — keep that in mind, it is the key to "the best of each group" later in this lesson.
ROW_NUMBER needs an order
Without ORDER BY inside OVER, ROW_NUMBER still runs, but the numbers follow whatever order the database happened to use — unpredictable. Always give it an ORDER BY.
Exercise 2 — Ties: RANK and DENSE_RANK
Levels are all different, so ROW_NUMBER never had to deal with a tie. Badges given per gym do tie: Ember, Boulder and Spark handed out 2 each. Count them in a CTE and compare the three numbering functions side by side:
WITH per_gym AS (
SELECT g.name, COUNT(b.badge_id) AS given
FROM gyms g
LEFT JOIN badges b ON b.gym_id = g.gym_id
GROUP BY g.gym_id
)
SELECT name, given,
ROW_NUMBER() OVER (ORDER BY given DESC) AS row_num,
RANK() OVER (ORDER BY given DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY given DESC) AS dense
FROM per_gym
ORDER BY given DESC, name;RANK() gives tied rows the same rank and then skips: after three gyms at rank 2, the next one is rank 5. DENSE_RANK() also shares the rank but never skips: Shade Gym is 3rd.
ROW_NUMBER(), by contrast, never ties — among the three equal gyms it hands out 2, 3 and 4 in an order you did not choose. Add a tiebreaker to make that order yours:
WITH per_gym AS (
SELECT g.name, COUNT(b.badge_id) AS given
FROM gyms g
LEFT JOIN badges b ON b.gym_id = g.gym_id
GROUP BY g.gym_id
)
SELECT name, given,
ROW_NUMBER() OVER (ORDER BY given DESC, name) AS row_num
FROM per_gym
ORDER BY row_num;Which one?
Olympic podium (1, 2, 2, 4): RANK. Compact levels (1, 2, 2, 3): DENSE_RANK. A unique number per row no matter what: ROW_NUMBER, with an ORDER BY complete enough to break ties.
Exercise 3 — After a tie
Values 10, 8, 8 and 5 are ranked with RANK() OVER (ORDER BY value DESC). Which ranks come out?
Hint : Two rows share rank 2, so rank 3 is used up and the next row is 4th.
Exercise 4 — Compact podium
Complete the query so that podium ranks the gyms by badges given, most generous first, without gaps: tied gyms share a rank and the next gym gets the very next number.
Hint : The ranking that never skips a number, and the keyword that sets the ranking order.
Exercise 5 — Buckets and ranked groups
One more numbering function: NTILE(n) deals the ordered rows into *n* buckets of (almost) equal size — handy for "the top half" or quartiles:
SELECT name, level, NTILE(2) OVER (ORDER BY level DESC) AS half
FROM codemon;Eight Codémons, two buckets of four: the top half is Aquachu, Pyrochu, Rockachu and Florachu.
Ranking also works on aggregated rows. Window functions run after GROUP BY, so you can rank the result of a COUNT directly, without a CTE:
SELECT trainer_id, COUNT(*) AS team_size,
RANK() OVER (ORDER BY COUNT(*) DESC) AS size_rank
FROM codemon
WHERE trainer_id IS NOT NULL
GROUP BY trainer_id
ORDER BY size_rank, trainer_id;COUNT(*) is computed by the GROUP BY first; the window then ranks those three counts. Taylor's team of 3 is first, Alex and Morgan tie at rank 2 — and nobody is 3rd.
Exercise 6 — Four numbering functions
Four rows have the values 9, 7, 7, 4, ordered descending. Match each function with what it produces.
ROW_NUMBER()RANK()DENSE_RANK()NTILE(2)Exercise 7 — Shade Gym's rank
Badges given: Tide 3, Ember 2, Boulder 2, Spark 2, Shade 1. With RANK() OVER (ORDER BY given DESC), Shade Gym gets:
Hint : Three gyms share rank 2, so ranks 3 and 4 are used up.
Exercise 8 — Two rankings at once
Professor Pi wants each Codémon's place in its team and in the whole league, side by side.
For the Codémons that have a trainer, return trainer_id, name, level, pos (ROW_NUMBER() inside the team, strongest first) and league_rank (RANK() over all these Codémons, strongest first). Sort by trainer_id, then pos.
-- Write your query below