RANK, DENSE_RANK, ROW_NUMBER in SQL

Three ways to number rows, and they disagree exactly when there is a tie.

SELECT
    name,
    score,
    ROW_NUMBER() OVER (
        ORDER BY score DESC
    ) AS position,
    RANK() OVER (
        ORDER BY score DESC
    ) AS rank_gaps,
    DENSE_RANK() OVER (
        ORDER BY score DESC
    ) AS rank_dense
FROM players;

How it works

ROW_NUMBER never ties: equal rows still get 1, 2, 3.
RANK gives ties the same number, then skips: 1, 1, 3.
DENSE_RANK gives ties the same number and skips nothing: 1, 1, 2.

Keywords and builtins used here

AS
BY
DESC
FROM
ORDER
SELECT
position

The run, in numbers

Lines: 13
Characters to type: 194
Tokens: 46
Three-star pace: 95 tpm

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

Type this snippet

Step 2 of 9 in Window functions, step 8 of 24 in Analytics & reporting.

← Previous Next →