RANK and DENSE_RANK assign rankings to rows. Understand the difference for accurate leaderboards.

RANK Function

SELECT

name,

score,

RANK() OVER (ORDER BY score DESC) as rank

FROM players;

-- Ties get same rank, next rank is skipped

DENSE_RANK Function

SELECT

name,

score,

DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank

FROM players;

-- Ties get same rank, next rank is NOT skipped

Comparison Example

-- Scores: 100, 100, 90, 80

-- RANK: 1, 1, 3, 4

-- DENSE_RANK: 1, 1, 2, 3

-- ROW_NUMBER: 1, 2, 3, 4

RANK with PARTITION

SELECT

department,

name,

salary,

RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank

FROM employees;

Find Top Performers

WITH ranked AS (

SELECT

name,

sales,

DENSE_RANK() OVER (ORDER BY sales DESC) as rank

FROM salespeople

)

SELECT * FROM ranked WHERE rank <= 3;

Generate Ranking Queries

AI2sql creates rankings automatically.