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?

Skip to the 4 exercises ↓

Syntax

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
RANK()
Or ROW_NUMBER(), or DENSE_RANK(). None of them takes an argument: what they count comes from OVER.
OVER (ORDER BY salary DESC)
The order to number the rows in. DESC gives number 1 to the highest salary. Two rows with the same salary are a tie.
PARTITION BY department
Optional, written before ORDER BY inside OVER: 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.

employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Live queryThe three side by side
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 9 rows
namesalaryrow_numrnkdense
Raj95000111
Zoe88000222
Kim88000322
Ivan81000443
Maya72000554
Omar58000665
Lena58000765
Nora46000886
Theo43000997

What each number means

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.

Live queryRow number 2: one of two people who earn the same
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 1 row
namesalary
Zoe88000

Which one to use

Decide what a tie should do, then pick the function that does it.

You wantUseOn this table
Exactly 3 rowsROW_NUMBER() <= 3, with a tie-breaker in the ORDER BYRaj, Zoe, Kim
The top 3 places, ties includedRANK() <= 3Raj, Zoe, Kim. Ivan is 4th: three people earn more.
The 3 highest salaries and everyone who earns themDENSE_RANK() <= 3Raj, Zoe, Kim, Ivan
The second highest salary (or the Nth)DENSE_RANK() = 2Zoe and Kim, 88000
One row per groupROW_NUMBER() = 1 with PARTITION BYOne 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.

Live queryTop 3 salaries: three functions, two answers
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 4 rows
namesalary
Raj95000
Zoe88000
Kim88000
Ivan81000
Live queryThird highest salary with RANK: no rows
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 0 rows
No rows

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.

Live queryThe best paid employee of each department, one row each
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Result · 3 rows
departmentnamesalary
EngineeringRaj95000
SalesMaya72000
SupportNora46000

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.

Exercise 1Rank by salary, with a gap after a tie

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC)
FROM employees
Result · 9 rows
namesalaryRANK() OVER (ORDER BY salary DESC)
Raj950001
Zoe880002
Kim880002
Ivan810004
Maya720005
Omar580006
Lena580006
Nora460008
Theo430009

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.

Exercise 2Rank inside each department, without gaps

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
Show the answer
SELECT department, name, salary,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC)
FROM employees
Result · 9 rows
departmentnamesalaryDENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC)
EngineeringRaj950001
EngineeringZoe880002
EngineeringKim880002
EngineeringIvan810003
SalesMaya720001
SalesOmar580002
SalesLena580002
SupportNora460001
SupportTheo430002

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.

Exercise 3The third highest salary

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
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
Result · 1 row
namesalary
Ivan81000

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.

Exercise 4One row per department

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
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
Ctrl/⌘ + Enter to run
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
Result · 3 rows
departmentnamesalary
EngineeringRaj95000
SalesMaya72000
SupportNora46000

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.

Keep going

Ten questions on a real database, from SELECT to JOINs and subqueries. Free, no sign-up.

Related

SQL window functions: OVER and PARTITION BY, live queriesSQL top N per group, with live queriesSQL second highest salary, with tiesSQL running total with SUM() OVERSQL interview questions, with answersSQL reference with queries you can runSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: SQLite documentation: Window Functions, PostgreSQL documentation: Window Functions (function list), PostgreSQL documentation: SELECT, MySQL 8.4 Reference Manual: Window Function Descriptions, Microsoft Learn: ROW_NUMBER (Transact-SQL). Last checked November 2026.