Example 1
Rank players on a leaderboard showing the difference between RANK and DENSE_RANK
Alice and Carol both score 100 and share rank 1. With RANK, Bob's next position is 3 (rank 2 is skipped because two players tied for 1st). With DENSE_RANK, Bob's next position is 2 — no gap. Dave follows at rank 4 (RANK) or rank 3 (DENSE_RANK).
Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE scores (id INT, player VARCHAR(50), score INT);
INSERT INTO
scores (id, player, score)
VALUES
(1, 'Alice', 100),
(2, 'Bob', 85),
(3, 'Carol', 100),
(4, 'Dave', 70);Validated query Shared across supported engines.
SQL
SELECT
player,
score,
RANK() OVER (
ORDER BY
score DESC
) AS rank_num,
DENSE_RANK() OVER (
ORDER BY
score DESC
) AS dense_rank_num
FROM
scores
ORDER BY
score DESC,
player;Expected result Returned rows for the shared example.
| player | score | rank_num | dense_rank_num |
|---|---|---|---|
| Alice | 100 | 1 | 1 |
| Carol | 100 | 1 | 1 |
| Bob | 85 | 3 | 2 |
| Dave | 70 | 4 | 3 |
Output is identical across all supported engine versions.