WindowSQL standard

RANK

Assigns places with ties: equal values share a place, and the next one is skipped.

Syntax

RANK() OVER (ORDER BY <sort key>)
RANK() OVER (PARTITION BY <group> ORDER BY <sort key>)

Just like in a competition: two people with the same result share second place, and the next one comes fourth, because third is already “taken”. A window function: the number of rows stays the same, and each gets its own place. PARTITION BY ranks separately within each group.

Data for the examples

employees

name	department	salary	bonus	phone
Emma	backend	1200	300	+44 7700 900123
Liam	backend	1000	NULL	NULL
Olivia	frontend	1350	150	+44 7700 900456
Noah	frontend	950	NULL	+44 7700 900789
Sophia	design	1200	200	NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

Places by salary

SELECT name, salary,

RANK() OVER (ORDER BY salary DESC) AS place

FROM employees;

name	salary	place
Olivia	1350	1
Emma	1200	2
Sophia	1200	2
Liam	1000	4
Noah	950	5

Emma and Sophia earn the same and share second place, so Liam is fourth.

ROW_NUMBER, RANK and DENSE_RANK side by side

SELECT name, salary,

ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number,

RANK() OVER (ORDER BY salary DESC) AS rank,

DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank

FROM employees;

name	salary	row_number	rank	dense_rank
Olivia	1350	1	1	1
Emma	1200	2	2	2
Sophia	1200	3	2	2
Liam	1000	4	4	3
Noah	950	5	5	4

The top two salaries, ties includedAdvanced

SELECT name, salary

FROM (

SELECT name, salary,

RANK() OVER (ORDER BY salary DESC) AS place

FROM employees

) ranked

WHERE place <= 2;

name	salary
Olivia	1350
Emma	1200
Sophia	1200

Three people come out, because two share second place. With ROW_NUMBER, one of them would drop out.

Watch out

After a tie, places are skipped: 1, 2, 2, 4. If you need no gaps — 1, 2, 2, 3 — use DENSE_RANK.

Without ORDER BY in OVER, all rows count as equal and get place 1.

You can't use the place in the WHERE of the same query — calculate it in a subquery first and filter outside it.

Related functions

DENSE_RANK ROW_NUMBER NTILE

Learn more in the course

RANK and DENSE_RANK