SQL DENSE_RANK(): Rank Without Gaps (Nth Highest Value)

DENSE_RANK() ranks rows like RANK: tied values share a rank. The difference is that DENSE_RANK never leaves gaps. After two rows tie for 1st, the next row is 2nd, not 3rd. In other words, it counts distinct values.

The example table#

scores (Maths)

Syntax#

DENSE_RANK() OVER (
  [PARTITION BY column]
  ORDER BY column
)

Dense ranking#

SELECT student, score,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense
FROM scores
WHERE subject = 'Maths'
ORDER BY dense, student;

student | score | dense
--------+-------+------
Asha    | 92    | 1
Meera   | 92    | 1
Priya   | 85    | 2
Ravi    | 85    | 2
John    | 78    | 3

There are three distinct scores (92, 85, 78), so the ranks are 1, 2 and 3. With RANK, John would be 5th.

The Nth highest value#

"Find the second-highest score" is a classic interview question, and DENSE_RANK handles ties correctly:

SELECT DISTINCT score
FROM (
  SELECT score,
         DENSE_RANK() OVER (ORDER BY score DESC) AS d
  FROM scores
  WHERE subject = 'Maths'
) t
WHERE d = 2;

score
-----
85

Compare the alternatives:

• ORDER BY score DESC LIMIT 1 OFFSET 1 returns 92 (Meera's tied score), which is wrong.
• ROW_NUMBER() ... = 2 also lands on the second 92.
• RANK() ... = 2 returns nothing, because no row has rank 2.

Only DENSE_RANK treats "second highest" as "second highest distinct value". Change d = 2 to d = N for any N.

Top N distinct values per group#

The top two distinct scores in each subject, including everyone who achieved them:

SELECT subject, student, score
FROM (
  SELECT subject, student, score,
         DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS d
  FROM scores
) t
WHERE d <= 2
ORDER BY subject, score DESC, student;

subject | student | score
--------+---------+------
Maths   | Asha    | 92
Maths   | Meera   | 92
Maths   | Priya   | 85
Maths   | Ravi    | 85
Science | Ravi    | 95
Science | Asha    | 88
Science | John    | 88

Which ranking function should I use?#

Common mistakes#

Practice#

SELECT name, department, salary
FROM (
  SELECT name, department, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS d
  FROM employees
) t
WHERE d = 2;

name | department  | salary
-----+-------------+-------
Ravi | Engineering | 78000
Asha | Sales       | 52000

Key takeaway#