WindowSQL standard

DENSE_RANK

Assigns places with ties, but without gaps: 1, 2, 2, 3.

Syntax

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

Equal values share a place, and the next value gets the next number, with no gap. Handy when it's the levels of values that matter: the highest salary, the second highest, the third.

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 without gaps

SELECT name, salary,

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

FROM employees;

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

Emma and Sophia share second place, and Liam is third. RANK would put him fourth.

RANK and DENSE_RANK side by side

SELECT name, salary,

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

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

FROM employees;

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

The second highest salaryAdvanced

SELECT name, salary

FROM (

SELECT name, salary,

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

FROM employees

) ranked

WHERE place = 2;

name	salary
Emma	1200
Sophia	1200

A classic interview question. DENSE_RANK finds exactly the second value, however many people share it.

Watch out

DENSE_RANK doesn't tell you how many people are above: Liam is in place 3, although three people come before him. If you need the position counting everyone, use RANK.

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

RANK ROW_NUMBER NTILE

Learn more in the course

RANK and DENSE_RANK