Level 17: Window functions: ranking

Lesson

GROUP BY sums up: it merges the rows of a group into one. Sometimes you want the opposite: keep all the rows and add to each one a piece of information that depends on the others: its rank, its number, its position in the group. That is the job of window functions, recognisable by the word OVER.

The “window” is the set of rows the function looks at to compute the value of the current row. OVER (ORDER BY rating DESC) means: look at all the rows, ordered by decreasing rating.

Three functions rank the rows. ROW_NUMBER() numbers 1, 2, 3… without ever repeating a number. RANK() gives the same rank to ties then skips places: 1, 2, 2, 4. DENSE_RANK() also gives the same rank to ties, but without gaps: 1, 2, 2, 3. In staff, Farid and Iris both earn 47000: RANK and DENSE_RANK put them on the same rank, ROW_NUMBER separates them.

When there is a tie, ROW_NUMBER breaks it in an arbitrary order, which may change. Add a tie-breaking column (ORDER BY salary DESC, name) to get a stable result.

PARTITION BY splits the rows into groups, and the calculation starts again in each group: RANK() OVER (PARTITION BY department ORDER BY salary DESC) ranks the employees within their department. Each department has its own number 1.

The ORDER BY written inside OVER is used for the calculation, not for the display: to sort the result, add a final ORDER BY.

Window functions are calculated after WHERE, GROUP BY and HAVING, just before the final ORDER BY. A WHERE therefore cannot filter on a rank: it does not exist yet. To keep “the first of each department”, you compute the rank in a CTE, then filter on it: this is the “top N per group” method.

Syntax

SELECT col, RANK() OVER (PARTITION BY grp ORDER BY value DESC) AS rnk
FROM my_table;

Worked example

SELECT title, rating, RANK() OVER (ORDER BY rating DESC) AS rk
FROM movies;

Each movie gets its rank by rating, and all 10 movies remain.

Example result

Key points

• OVER (…) signals a window function.
• ROW_NUMBER: unique numbers; RANK: ties then a gap; DENSE_RANK: ties without a gap.
• PARTITION BY = “within each group”.

Common pitfalls

• WHERE RANK() OVER (…) = 1 is not allowed: go through a CTE.
• The ORDER BY inside OVER orders the rows for the calculation; it doesn't sort the final result.
• ROW_NUMBER without a tie-breaker can pick a different winner from one run to the next when there's a tie.

Going further

NTILE(4) OVER (ORDER BY salary) splits the rows into 4 groups of equal size (quartiles); PERCENT_RANK gives each row's relative position, between 0 and 1.

The level’s 5 exercises

1. Guided · Show the title and release date of the songs, with a column n that numbers them from oldest (1) to newest. (Table: songs)
2. Practice · Show the name and salary of the employees with their RANK and their DENSE_RANK, from highest to lowest salary. (Table: staff)
3. Practice · Show the name, department and salary of each employee with their salary rank within their department (1 = best paid in the department). (Table: staff)
4. Practice · Show the best-paid employee of each department (name, department, salary). (Table: staff)
5. Challenge · For each race (race), show the two fastest runners: race, runner's name and time, sorted by race then by ranking. (Tables: results, runners)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic