MySQL RANK() and DENSE_RANK(): Ranking with Ties

When values can tie, a ranking should give tied rows the same position. MySQL has several functions for that, and they differ in what happens after a tie.

query.sql

SQL

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    manager_id INT NULL,
    hired_on DATE NOT NULL
);

INSERT INTO employees (name, department, salary, manager_id, hired_on) VALUES
    ('Asha Rao',     'management',  150000.00, NULL, '2020-01-15'),
    ('Ben Carter',   'engineering', 120000.00,    1, '2021-03-01'),
    ('Chitra Nair',  'engineering',  95000.00,    2, '2022-07-11'),
    ('Dev Malhotra', 'engineering',  88000.00,    2, '2023-01-09'),
    ('Esha Gupta',   'sales',        70000.00,    1, '2021-09-20'),
    ('Farid Khan',   'sales',        64000.00,    5, '2022-11-02'),
    ('Gita Menon',   'sales',        64000.00,    5, '2024-02-14'),
    ('Hari Iyer',    'support',      52000.00,    1, '2023-05-30');

Look at the salaries: Farid and Gita both earn 64,000, so they tie.

query.sql

SQL

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_no,
       RANK()       OVER (ORDER BY salary DESC) AS rank_no,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_no
FROM employees
ORDER BY salary DESC, name;

Output

OUTPUT

+--------------+-----------+--------+---------+---------------+
| name         | salary    | row_no | rank_no | dense_rank_no |
+--------------+-----------+--------+---------+---------------+
| Asha Rao     | 150000.00 |      1 |       1 |             1 |
| Ben Carter   | 120000.00 |      2 |       2 |             2 |
| Chitra Nair  |  95000.00 |      3 |       3 |             3 |
| Dev Malhotra |  88000.00 |      4 |       4 |             4 |
| Esha Gupta   |  70000.00 |      5 |       5 |             5 |
| Farid Khan   |  64000.00 |      6 |       6 |             6 |
| Gita Menon   |  64000.00 |      7 |       6 |             6 |
| Hari Iyer    |  52000.00 |      8 |       8 |             7 |
+--------------+-----------+--------+---------+---------------+
8 rows in set

Function	Behaviour for ties	After a tie
`ROW_NUMBER()`	Different numbers (arbitrary order)	Continues 1, 2, 3, 4, 5, 6, 7, 8
`RANK()`	Same rank	Skips numbers: 6, 6, then 8
`DENSE_RANK()`	Same rank	No gaps: 6, 6, then 7

Farid and Gita are both rank 6. With RANK(), the next person is rank 8 (positions 6 and 7 were “used”). With DENSE_RANK(), the next is 7.

Which is right depends on the question:

“Position in a race”: RANK() (two silver medals, no bronze, then 4th place).
“How many distinct salary levels are above this one?”: DENSE_RANK().

Rank within groups

query.sql

SQL

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

Output

OUTPUT

+-------------+--------------+-----------+-----------+
| department  | name         | salary    | dept_rank |
+-------------+--------------+-----------+-----------+
| engineering | Ben Carter   | 120000.00 |         1 |
| engineering | Chitra Nair  |  95000.00 |         2 |
| engineering | Dev Malhotra |  88000.00 |         3 |
| management  | Asha Rao     | 150000.00 |         1 |
| sales       | Esha Gupta   |  70000.00 |         1 |
| sales       | Farid Khan   |  64000.00 |         2 |
| sales       | Gita Menon   |  64000.00 |         2 |
| support     | Hari Iyer    |  52000.00 |         1 |
+-------------+--------------+-----------+-----------+
8 rows in set

The Nth highest salary

A famous interview question. With DENSE_RANK, the Nth distinct value is easy:

query.sql

SQL

1
2
3

SELECT DISTINCT salary AS third_highest_salary
FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM employees) AS t
WHERE dr = 3;

Output

OUTPUT

+----------------------+
| third_highest_salary |
+----------------------+
|             95000.00 |
+----------------------+
1 row in set

Distinct salaries ranked from the top: 150,000, 120,000, 95,000. The third highest is 95,000. See Interview Questions for more.

PERCENT_RANK and CUME_DIST

Two functions express the position as a fraction between 0 and 1:

PERCENT_RANK() = (rank – 1) / (rows in the partition – 1). The first row is 0, the last is 1.
CUME_DIST() = (rows with a value less than or equal to the current one) / (total rows). It is the share of rows at or before this one.

query.sql

SQL

SELECT name, salary,
       RANK() OVER (ORDER BY salary) AS rank_from_bottom,
       ROUND(PERCENT_RANK() OVER (ORDER BY salary), 3) AS percent_rank_value,
       ROUND(CUME_DIST() OVER (ORDER BY salary), 3) AS cumulative_distribution
FROM employees
ORDER BY salary, name;

Output

OUTPUT

+--------------+-----------+------------------+--------------------+-------------------------+
| name         | salary    | rank_from_bottom | percent_rank_value | cumulative_distribution |
+--------------+-----------+------------------+--------------------+-------------------------+
| Hari Iyer    |  52000.00 |                1 |                  0 |                   0.125 |
| Farid Khan   |  64000.00 |                2 |              0.143 |                   0.375 |
| Gita Menon   |  64000.00 |                2 |              0.143 |                   0.375 |
| Esha Gupta   |  70000.00 |                4 |              0.429 |                     0.5 |
| Dev Malhotra |  88000.00 |                5 |              0.571 |                   0.625 |
| Chitra Nair  |  95000.00 |                6 |              0.714 |                    0.75 |
| Ben Carter   | 120000.00 |                7 |              0.857 |                   0.875 |
| Asha Rao     | 150000.00 |                8 |                  1 |                       1 |
+--------------+-----------+------------------+--------------------+-------------------------+
8 rows in set

Hari (the lowest) has percent_rank = 0 and cume_dist = 0.125 (1 of 8 rows at or below). Asha (the highest) has percent_rank = 1 and cume_dist = 1. Farid and Gita tie: they share the same values.

Use CUME_DIST() for “in the bottom X percent” and PERCENT_RANK() for a relative standing.

Top group members with ties kept

“The top 3 earners, including ties”:

query.sql

SQL

SELECT name, salary, rank_no
FROM (SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank_no FROM employees) AS t
WHERE rank_no <= 3
ORDER BY rank_no, name;

Output

OUTPUT

+-------------+-----------+---------+
| name        | salary    | rank_no |
+-------------+-----------+---------+
| Asha Rao    | 150000.00 |       1 |
| Ben Carter  | 120000.00 |       2 |
| Chitra Nair |  95000.00 |       3 |
+-------------+-----------+---------+
3 rows in set

With no ties in the top three, three rows return. If the third and fourth tied, RANK() would return four rows (Top-N Queries).

Ranking with several sort keys

query.sql

SQL

SELECT name, department, hired_on,
       RANK() OVER (PARTITION BY department ORDER BY hired_on) AS seniority_in_department
FROM employees
ORDER BY department, seniority_in_department, name;

Output

OUTPUT

+--------------+-------------+------------+-------------------------+
| name         | department  | hired_on   | seniority_in_department |
+--------------+-------------+------------+-------------------------+
| Ben Carter   | engineering | 2021-03-01 |                       1 |
| Chitra Nair  | engineering | 2022-07-11 |                       2 |
| Dev Malhotra | engineering | 2023-01-09 |                       3 |
| Asha Rao     | management  | 2020-01-15 |                       1 |
| Esha Gupta   | sales       | 2021-09-20 |                       1 |
| Farid Khan   | sales       | 2022-11-02 |                       2 |
| Gita Menon   | sales       | 2024-02-14 |                       3 |
| Hari Iyer    | support     | 2023-05-30 |                       1 |
+--------------+-------------+------------+-------------------------+
8 rows in set

Remember

RANK, DENSE_RANK and the others need an ORDER BY in the window. Without it all rows tie for rank 1.
A rank is determined by the window’s ORDER BY columns only. Two rows with the same values in those columns tie.
RANK and ROW_NUMBER are reserved words in MySQL 8: do not use them as alias or column names without backticks.

lightbulb

Decide what ties mean

Before you pick a function, say aloud what should happen when two rows tie, and what the next row’s number should be. That answer picks the function.