Ranking within a partition in SQL

Window functions rank rows without collapsing them into groups.

SELECT
    country,
    name,
    score,
    RANK() OVER (
        PARTITION BY country
        ORDER BY score DESC
    ) AS country_rank,
    ROW_NUMBER() OVER (
        ORDER BY score DESC
    ) AS overall
FROM players;

How it works

1. PARTITION BY restarts the ranking for each group.
2. RANK ties share a number, ROW_NUMBER never repeats.
3. The detail rows survive, unlike with GROUP BY.

Keywords and builtins used here

• AS
• BY
• DESC
• FROM
• ORDER
• SELECT

The run, in numbers

Lines

12

Characters to type

169

Tokens

38

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 24 seconds.

Type this snippet

Step 1 of 9 in Window functions, step 7 of 24 in Analytics & reporting.

← Previous Next →