SQL practice › SQL reference › RANK vs DENSE_RANK vs ROW_NUMBER
RANK vs DENSE_RANK vs ROW_NUMBER in SQL: what each one does with ties
All three number rows in an order you choose, and they only differ when rows tie. ROW_NUMBER() gives every row its own number: 1, 2, 3, 4. RANK() gives tied rows the same number and then skips: 1, 2, 2, 4. DENSE_RANK() gives tied rows the same number and doesn't skip: 1, 2, 2, 3.
When no two rows tie, the three return the same numbers. So choosing between them is answering one question: what should happen to a tie?
Syntax
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employeesRANK()- Or
ROW_NUMBER(), orDENSE_RANK(). None of them takes an argument: what they count comes fromOVER. OVER (ORDER BY salary DESC)- The order to number the rows in.
DESCgives number 1 to the highest salary. Two rows with the same salary are a tie. PARTITION BY department- Optional, written before
ORDER BYinsideOVER: the numbering then starts again at 1 in each department.
The data
Nine employees. Two pairs earn the same salary: Zoe and Kim both earn 88000, Omar and Lena both earn 58000. Those two ties are where the three functions part ways.
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | salary | row_num | rnk | dense |
|---|---|---|---|---|
| Raj | 95000 | 1 | 1 | 1 |
| Zoe | 88000 | 2 | 2 | 2 |
| Kim | 88000 | 3 | 2 | 2 |
| Ivan | 81000 | 4 | 4 | 3 |
| Maya | 72000 | 5 | 5 | 4 |
| Omar | 58000 | 6 | 6 | 5 |
| Lena | 58000 | 7 | 6 | 5 |
| Nora | 46000 | 8 | 8 | 6 |
| Theo | 43000 | 9 | 9 | 7 |
ROW_NUMBER gives them 2 and 3, the other two give both of them 2. The next row, Ivan, is where RANK and DENSE_RANK split: 4 against 3. By the last row, Theo is 9, 9 and 7.What each number means
- ROW_NUMBER is a place in line. Nine rows get the numbers 1 to 9, whatever their values.
- RANK is one more than the number of rows ahead of you. Three people earn more than Ivan, so he is 4. That is how a race is scored: two silver medals, no bronze.
- DENSE_RANK is one more than the number of different values ahead of you. Two salaries are higher than Ivan's (95000 and 88000), so he is 3. It numbers the salaries, not the people.
ROW_NUMBER breaks ties, and it doesn't say how
Zoe got 2 and Kim got 3, though they earn the same. Nothing in ORDER BY salary DESC puts one before the other, so the database picked. It is allowed to pick differently the next time: on other data, after an update, or in another database. That matters as soon as you filter on the number.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | salary |
|---|---|
| Zoe | 88000 |
ORDER BY to salary DESC, name and the answer is Kim, every time, because K comes before Z.Which one to use
Decide what a tie should do, then pick the function that does it.
| You want | Use | On this table |
|---|---|---|
| Exactly 3 rows | ROW_NUMBER() <= 3, with a tie-breaker in the ORDER BY | Raj, Zoe, Kim |
| The top 3 places, ties included | RANK() <= 3 | Raj, Zoe, Kim. Ivan is 4th: three people earn more. |
| The 3 highest salaries and everyone who earns them | DENSE_RANK() <= 3 | Raj, Zoe, Kim, Ivan |
| The second highest salary (or the Nth) | DENSE_RANK() = 2 | Zoe and Kim, 88000 |
| One row per group | ROW_NUMBER() = 1 with PARTITION BY | One employee per department |
The query below computes all three and filters on one. Change the column in the last line to see each row of the table.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| name | salary |
|---|---|
| Raj | 95000 |
| Zoe | 88000 |
| Kim | 88000 |
| Ivan | 81000 |
dense to rnk: three rows, without Ivan. With row_num it is three rows as well. Now change the 3 to a 2: rnk <= 2 still returns Raj, Zoe and Kim, and row_num <= 2 returns two rows, cutting one of the two people on 88000.Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| No rows |
RANK() to DENSE_RANK() and you get Ivan with 81000. For "the Nth highest value", DENSE_RANK is the one that never skips a number.ROW_NUMBER with PARTITION BY: one row per group
Because ROW_NUMBER never repeats a number, = 1 returns exactly one row per partition. That makes it the tool for "one row per group": the best paid person of each department, the latest order of each customer, one copy of each duplicate. Put a tie-breaker in the ORDER BY so that you choose which row it is. More on this pattern in top N per group.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| department | name | salary |
|---|---|---|
| Engineering | Raj | 95000 |
| Sales | Maya | 72000 |
| Support | Nora | 46000 |
PARTITION BY department restarts the numbering in each department. hire_date is the tie-breaker: if two people shared a department's top salary, the one hired first would be number 1. Change = 1 to = 2: in Engineering and Sales second place is a tie, and the hire date settles it (Zoe and Omar).Practice: 4 exercises
Write the query in the box and press Run. Your result is compared with the right one, on this table and again on changed data, so a query with the values typed in won't pass. Column names don't matter; the columns and rows do.
Return each employee's name, salary and their rank by salary in the company: the highest salary is 1, people with the same salary share a rank, and the rank after a tie skips, as in 1, 2, 2, 4.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC)
FROM employees| name | salary | RANK() OVER (ORDER BY salary DESC) |
|---|---|---|
| Raj | 95000 | 1 |
| Zoe | 88000 | 2 |
| Kim | 88000 | 2 |
| Ivan | 81000 | 4 |
| Maya | 72000 | 5 |
| Omar | 58000 | 6 |
| Lena | 58000 | 6 |
| Nora | 46000 | 8 |
| Theo | 43000 | 9 |
RANK() gives tied rows the same number and then jumps by the size of the tie: Zoe and Kim are both 2, Ivan is 4. Omar and Lena are both 6, and Nora is 8.
Return each employee's department, name, salary and the rank of their salary inside their own department: the highest is 1, the same salary gets the same rank, and no number is skipped, as in 1, 2, 2, 3.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
SELECT department, name, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC)
FROM employees| department | name | salary | DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) |
|---|---|---|---|
| Engineering | Raj | 95000 | 1 |
| Engineering | Zoe | 88000 | 2 |
| Engineering | Kim | 88000 | 2 |
| Engineering | Ivan | 81000 | 3 |
| Sales | Maya | 72000 | 1 |
| Sales | Omar | 58000 | 2 |
| Sales | Lena | 58000 | 2 |
| Support | Nora | 46000 | 1 |
| Support | Theo | 43000 | 2 |
PARTITION BY department ranks each department on its own, and DENSE_RANK numbers the different salaries 1, 2, 3 without gaps. In Engineering, Raj is 1, Zoe and Kim are 2 and Ivan is 3. RANK would make Ivan 4.
Return the name and salary of everyone who earns the third highest salary in the company. Equal salaries count once: with 95000, 88000, 88000 and 81000, the third highest is 81000.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
WITH ranked AS (
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS d
FROM employees
)
SELECT name, salary
FROM ranked
WHERE d = 3| name | salary |
|---|---|
| Ivan | 81000 |
DENSE_RANK numbers the distinct salaries: 95000 is 1, 88000 is 2, 81000 is 3. The filter has to sit outside the CTE, because a window function can't go in WHERE. Only Ivan earns 81000. A subquery with SELECT DISTINCT salary ... LIMIT 1 OFFSET 2 finds the same salary.
For each department return the department, and the name and salary of its best paid employee: exactly one row per department. If two people share a department's top salary, take the one hired first (the earliest hire_date).
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
Show the answer
WITH numbered AS (
SELECT department, name, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, hire_date) AS n
FROM employees
)
SELECT department, name, salary
FROM numbered
WHERE n = 1| department | name | salary |
|---|---|---|
| Engineering | Raj | 95000 |
| Sales | Maya | 72000 |
| Support | Nora | 46000 |
ROW_NUMBER never repeats a number inside a partition, so n = 1 is exactly one row per department. RANK would return both people when the top salary is shared. The second sort key, hire_date, decides who gets number 1 in that case, so the result doesn't depend on how the database happens to order a tie.
Questions people ask
What is the difference between RANK and DENSE_RANK?
Both give tied rows the same number. RANK then skips as many numbers as the tie took, so after two rows at 2 the next is 4. DENSE_RANK continues with the next number, 3. RANK tells you how many rows are ahead; DENSE_RANK tells you how many different values are ahead.
What is the difference between ROW_NUMBER and RANK?
ROW_NUMBER numbers all rows one after the other, 1, 2, 3, 4, 5, even when their values are equal. RANK gives equal values the same number: 1, 2, 2, 4, 5. With no ties they return the same thing.
Is the result of ROW_NUMBER always the same?
Only when the ORDER BY inside OVER can't tie. If two rows are equal on every sort column, the database may number them either way, and may not do it the same way on every run. Add columns to the ORDER BY until it is unique (an id is the usual last one). If equal rows should get equal numbers, use RANK or DENSE_RANK, which are defined to give all rows of a tie the same answer.
Do these functions need an ORDER BY?
They need one to mean anything. SQLite and MySQL accept ROW_NUMBER() OVER () and number the rows in no particular order, and SQL Server's documentation says the ORDER BY is required for ROW_NUMBER. RANK and DENSE_RANK without an ORDER BY treat all rows as tied, so every row gets 1.
How does ROW_NUMBER() OVER (PARTITION BY ...) work?
PARTITION BY splits the rows into groups and the numbering starts again at 1 in each group, in the order given by the ORDER BY that follows it. ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) numbers each department's employees from the best paid down. Filter on that number in an outer query to keep the first row, or the first N, of each group.