DENSE_RANK()

Syntax

How it works

DENSE_RANK() is a ranking window function. For every row it looks at the values in the OVER (ORDER BY ...) clause and assigns a rank starting at 1. Rows that tie on the ordering expression receive the same rank, and the very next distinct value gets the next integer, so the sequence never skips a number.

The word "dense" is the key idea. If two rows tie for first place, both are rank 1 and the following row is rank 2, giving 1, 1, 2. Compare that with RANK(), which leaves a gap and produces 1, 1, 3, and with ROW_NUMBER(), which never ties and produces 1, 2, 3 even when the values are equal. Choosing between the three is one of the most common SQL interview topics.

Like all window functions, DENSE_RANK() keeps every input row instead of collapsing groups the way an aggregate would. The optional PARTITION BY restarts the rank for each group, so you can rank employees inside each department, products inside each category, and so on. The ORDER BY inside OVER() is required, because a rank has no meaning without a defined order.

Examples

DENSE_RANK vs RANK vs ROW_NUMBER side by side

SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
       RANK()       OVER (ORDER BY score DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM players
ORDER BY score DESC;

name  | score | row_num | rnk | dense_rnk
------+-------+---------+-----+----------
 Ali  |   100 |       1 |   1 |         1
 Sara |   100 |       2 |   1 |         1
 Omar |    90 |       3 |   3 |         2
 Lina |    80 |       4 |   4 |         3

Dense rank across the whole table

SELECT product, price,
       DENSE_RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products
ORDER BY price DESC;

product | price | price_rank
--------+-------+-----------
 Laptop |  1200 |          1
 Phone  |  1200 |          1
 Tablet |   800 |          2
 Mouse  |    25 |          3

Rank within groups using PARTITION BY

SELECT department, name, salary,
       DENSE_RANK() OVER (
         PARTITION BY department
         ORDER BY salary DESC
       ) AS dept_rank
FROM employees
ORDER BY department, dept_rank;

department | name  | salary | dept_rank
-----------+-------+--------+----------
 Eng       | Ali   |   9000 |         1
 Eng       | Sara  |   9000 |         1
 Eng       | Omar  |   7000 |         2
 Sales     | Lina  |   6000 |         1
 Sales     | Zaid  |   5000 |         2

Nth highest distinct salary (classic interview query)

-- Third highest DISTINCT salary. DENSE_RANK treats
-- ties as one rank, so "distinct" is handled for you.
WITH ranked AS (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees
)
SELECT DISTINCT salary
FROM ranked
WHERE r = 3;

salary
------
 7000

Top 2 distinct prices per category

WITH ranked AS (
  SELECT category, product, price,
         DENSE_RANK() OVER (
           PARTITION BY category
           ORDER BY price DESC
         ) AS r
  FROM products
)
SELECT category, product, price
FROM ranked
WHERE r <= 2
ORDER BY category, price DESC;

category | product | price
---------+---------+------
 Audio   | Speaker |   300
 Audio   | Buds    |   150
 Video   | TV      |  1200
 Video   | Cam     |   900

Common mistakes

-- ROW_NUMBER gives tied rows different numbers,
-- so "third highest distinct salary" breaks when
-- the top salaries are tied.
WITH ranked AS (
  SELECT salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC) AS r
  FROM employees
)
SELECT salary FROM ranked WHERE r = 3;

-- DENSE_RANK collapses ties into one rank, so
-- rank 3 is genuinely the third distinct salary.
WITH ranked AS (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees
)
SELECT DISTINCT salary FROM ranked WHERE r = 3;

-- No ORDER BY inside OVER: this is an error or
-- gives every row the same meaningless rank.
SELECT name, salary,
       DENSE_RANK() OVER (PARTITION BY department) AS r
FROM employees;

SELECT name, salary,
       DENSE_RANK() OVER (
         PARTITION BY department
         ORDER BY salary DESC
       ) AS r
FROM employees;

-- Expecting gaps after ties. DENSE_RANK never
-- skips, so this filter will not find "rank 3"
-- the way RANK would.
SELECT name FROM (
  SELECT name,
         DENSE_RANK() OVER (ORDER BY score DESC) AS r
  FROM players
) t
WHERE r = 3;  -- with RANK a tie at top would make 3 the 3rd row

-- Use RANK() when you want the position to reflect
-- how many rows came before, including ties.
SELECT name FROM (
  SELECT name,
         RANK() OVER (ORDER BY score DESC) AS r
  FROM players
) t
WHERE r = 3;

Performance

Like every ranking window function, DENSE_RANK() needs the rows sorted by the OVER (ORDER BY ...) expression, and by the PARTITION BY columns when present. That sort is the main cost. On large result sets it can spill to disk and dominate the query time, so it is worth measuring with your engine EXPLAIN plan.

An index whose column order matches PARTITION BY followed by ORDER BY lets the optimiser read rows already in order and skip the sort entirely. See the indexing guide for how ordered index scans avoid a separate sort step.

Computing several ranking functions that share the same OVER() clause is cheap, because the engine sorts once and evaluates ROW_NUMBER(), RANK() and DENSE_RANK() in the same pass. Ranking on a partition or expression different from your indexes forces an extra sort, so keep the window definitions aligned where you can.

Interview questions

SELECT name, score,
       ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
       RANK()       OVER (ORDER BY score DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM players;

WITH ranked AS (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees
)
SELECT DISTINCT salary
FROM ranked
WHERE r = 3;  -- N = 3