ROW_NUMBER, RANK and DENSE_RANK

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

▶ Do the exercises

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

Options
• 1, 2, 2, 4
• 1, 2, 2, 3
• 1, 2, 3, 4
• 1, 1, 2, 3

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.

Items to match
• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• NTILE(2)
Definitions
• 1, 2, 3, 4 — never a tie
• 1, 2, 2, 4 — ties share, then a gap
• 1, 2, 2, 3 — ties share, no gap
• 1, 1, 2, 2 — two buckets of equal size

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:

Options
• 5
• 3
• 4
• 2

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.

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