SQL Tutorial › Window Functions › ROW_NUMBER, RANK, DENSE_RANK

SQL ROW_NUMBER, RANK and DENSE_RANK

Q: What is the difference between RANK and DENSE_RANK?

Both give tied rows the same rank. RANK then skips numbers (1, 2, 2, 4); DENSE_RANK continues without gaps (1, 2, 2, 3).

Q: What is the difference between ROW_NUMBER and RANK?

ROW_NUMBER always gives unique, consecutive numbers, even to ties. RANK gives ties the same number.

Q: How do I find the Nth highest salary in SQL?

Use DENSE_RANK() OVER (ORDER BY salary DESC) in a CTE and filter on the rank you need. Add PARTITION BY dept_id for the Nth highest per department.

Q: How do I delete duplicate rows using ROW_NUMBER?

Number the rows of each duplicate group with ROW_NUMBER() OVER (PARTITION BY duplicate_columns ORDER BY id) and delete the rows with a number greater than 1.

AdvancedLesson 46 of 6911 min read7 queries run on MySQL 8.4

ROW_NUMBER, RANK and DENSE_RANK all number rows in order, and they differ only when there are ties. Interviewers love that difference, and real reports depend on it: should two people with the same score share second place, and is the next one third or fourth? This lesson shows all three side by side on data with a tie.

In this lesson

The exact difference between ROW_NUMBER, RANK and DENSE_RANK
Rank inside groups with PARTITION BY
Find the Nth highest value correctly with ties
Split rows into buckets with NTILE

Sample data

Priya and Karan both earn 72,000: the tie that makes the functions differ.

employees · 12 rows (some columns)

emp_id	first_name	dept_id	salary
100	Meera	NULL	250000
101	Asha	1	125000
102	Rahul	1	98000
103	Priya	1	72000
104	Karan	1	72000
105	Vikram	2	105000
106	Neha	2	54000
107	Arjun	2	48000
108	Kavya	3	88000
109	Rohan	3	45000
110	Sneha	4	78000
111	Amit	1	25000

The three functions side by side

SQL

SELECT first_name, salary,       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,       RANK()       OVER (ORDER BY salary DESC) AS rnk,       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnkFROM employeesORDER BY salary DESC;

ResultMySQL 8.4

first_name	salary	row_num	rnk	dense_rnk
Meera	250000	1	1	1
Asha	125000	2	2	2
Vikram	105000	3	3	3
Rahul	98000	4	4	4
Kavya	88000	5	5	5
Sneha	78000	6	6	6
Priya	72000	7	7	7
Karan	72000	8	7	7
Neha	54000	9	9	8
Arjun	48000	10	10	9
Rohan	45000	11	11	10
Amit	25000	12	12	11

12 rows in set

How this query works, step by step

All three order the rows by salary, highest first.
ROW_NUMBER gives every row a unique number: the tied pair gets 7 and 8 in an arbitrary order.
RANK gives tied rows the same rank (7) and then skips: the next salary is ranked 9, like positions in a race.
DENSE_RANK gives tied rows the same rank (7) and does not skip: the next is 8.

Function	Ties get	After a tie	Use for
`ROW_NUMBER()`	different numbers	continues	unique numbering, pagination, de-duplication
`RANK()`	the same number	skips (1, 2, 2, 4)	competition ranking
`DENSE_RANK()`	the same number	no gap (1, 2, 2, 3)	“Nth highest value” questions

Watch out

With ties, ROW_NUMBER decides the order of tied rows arbitrarily. Add a tie-breaker, such as ORDER BY salary DESC, emp_id, when the numbering must be repeatable.

Rank inside each group

PARTITION BY restarts the numbering for each department:

SQL

SELECT dept_id, first_name, salary,       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_deptFROM employeesWHERE dept_id IS NOT NULLORDER BY dept_id, rank_in_dept;

ResultMySQL 8.4

dept_id	first_name	salary	rank_in_dept
1	Asha	125000	1
1	Rahul	98000	2
1	Priya	72000	3
1	Karan	72000	3
1	Amit	25000	4
2	Vikram	105000	1
2	Neha	54000	2
2	Arjun	48000	3
3	Kavya	88000	1
3	Rohan	45000	2
4	Sneha	78000	1

11 rows in set

How this query works, step by step

Each department is ranked separately; every department’s top earner gets 1.
In Engineering, Priya and Karan share rank 3 and Amit is 4.

Nth highest salary

The interview classic: the third highest distinct salary. With DENSE_RANK, a tie does not swallow a position:

SQL

WITH ranked AS (  SELECT first_name, salary,         DENSE_RANK() OVER (ORDER BY salary DESC) AS dr  FROM employees)SELECT first_name, salaryFROM rankedWHERE dr = 3;

ResultMySQL 8.4

first_name	salary
Vikram	105000

1 row in set

How this query works, step by step

The distinct salaries in order are 250,000, 125,000, 105,000, 98,000…
The third distinct salary is 105,000, and dr = 3 returns the person who earns it: Vikram.
Unlike LIMIT 1 OFFSET 2, this returns every person at that salary if several share it.

Second highest salary per department:

SQL

WITH ranked AS (  SELECT dept_id, first_name, salary,         DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dr  FROM employees  WHERE dept_id IS NOT NULL)SELECT dept_id, first_name, salaryFROM rankedWHERE dr = 2ORDER BY dept_id;

ResultMySQL 8.4

dept_id	first_name	salary
1	Rahul	98000
2	Neha	54000
3	Rohan	45000

3 rows in set

HR has only one employee, so it has no second salary and does not appear.

Remove duplicates with ROW_NUMBER

Keep only the first row of each duplicate group. Here, one employee per salary value (the lowest emp_id):

SQL

WITH numbered AS (  SELECT emp_id, first_name, salary,         ROW_NUMBER() OVER (PARTITION BY salary ORDER BY emp_id) AS rn  FROM employees)SELECT emp_id, first_name, salaryFROM numberedWHERE rn = 1ORDER BY salary DESC;

ResultMySQL 8.4

emp_id	first_name	salary
100	Meera	250000
101	Asha	125000
105	Vikram	105000
102	Rahul	98000
108	Kavya	88000
110	Sneha	78000
103	Priya	72000
106	Neha	54000
107	Arjun	48000
109	Rohan	45000
111	Amit	25000

11 rows in set

How this query works, step by step

PARTITION BY salary groups rows with the same salary.
ROW_NUMBER numbers them 1, 2, … by emp_id.
Keeping rn = 1 keeps one row per salary: Karan (104) is dropped because Priya (103) comes first.
To actually delete duplicates, delete the rows whose rn > 1.

NTILE: split into buckets

NTILE(n) divides the ordered rows into n groups of (nearly) equal size, for quartiles or “top third”:

SQL

SELECT first_name, salary,       NTILE(4) OVER (ORDER BY salary DESC) AS quartileFROM employeesORDER BY salary DESC;

ResultMySQL 8.4

first_name	salary	quartile
Meera	250000	1
Asha	125000	1
Vikram	105000	1
Rahul	98000	2
Kavya	88000	2
Sneha	78000	2
Priya	72000	3
Karan	72000	3
Neha	54000	3
Arjun	48000	4
Rohan	45000	4
Amit	25000	4

12 rows in set

How this query works, step by step

12 rows split into 4 buckets of 3.
Quartile 1 holds the top three salaries, quartile 4 the bottom three.
If the rows do not divide evenly, the first buckets get one extra row.

PERCENT_RANK and CUME_DIST

SQL

SELECT first_name, salary,       ROUND(PERCENT_RANK() OVER (ORDER BY salary), 2) AS pct_rank,       ROUND(CUME_DIST()    OVER (ORDER BY salary), 2) AS cumulative_distFROM employeesWHERE dept_id = 1;

ResultMySQL 8.4

first_name	salary	pct_rank	cumulative_dist
Amit	25000	0	0.2
Priya	72000	0.25	0.6
Karan	72000	0.25	0.6
Rahul	98000	0.75	0.8
Asha	125000	1	1

5 rows in set

PERCENT_RANK = (rank − 1) / (rows − 1): 0 for the lowest, 1 for the highest.
CUME_DIST = share of rows with a value less than or equal to the current one.

Exercise: Rank products by price

Rank products by price from the most expensive within each category with RANK(), and show category, product name, price and rank.

Expected resultMySQL 8.4

category	product_name	price	price_rank
Electronics	Mechanical Keyboard	3499	1
Electronics	USB-C Hub	1999	2
Electronics	Wireless Mouse	799	3
Furniture	Standing Desk	18999	1
Furniture	Office Chair	8999	2
Furniture	Monitor Arm	2799	3
Furniture	Desk Lamp	1499	4
Stationery	Notebook Pack	249	1
Stationery	Gel Pens	199	2

9 rows in set

Hint 1

RANK() OVER (PARTITION BY category ORDER BY price DESC).

SQL

SELECT category, product_name, price,       RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rankFROM productsORDER BY category, price_rank;

ResultMySQL 8.4

category	product_name	price	price_rank
Electronics	Mechanical Keyboard	3499	1
Electronics	USB-C Hub	1999	2
Electronics	Wireless Mouse	799	3
Furniture	Standing Desk	18999	1
Furniture	Office Chair	8999	2
Furniture	Monitor Arm	2799	3
Furniture	Desk Lamp	1499	4
Stationery	Notebook Pack	249	1
Stationery	Gel Pens	199	2

9 rows in set

Quick quiz

1. Salaries 100, 90, 90, 80. What does RANK() give the 80?

2. Same salaries. What does DENSE_RANK() give the 80?

3. Which function never gives two rows the same number?

4. Which is best for “the 2nd highest distinct salary”?

Ranking FAQ

What is the difference between RANK and DENSE_RANK?

Both give tied rows the same rank. RANK then skips numbers (1, 2, 2, 4); DENSE_RANK continues without gaps (1, 2, 2, 3).

What is the difference between ROW_NUMBER and RANK?

ROW_NUMBER always gives unique, consecutive numbers, even to ties. RANK gives ties the same number.

How do I find the Nth highest salary in SQL?

Use DENSE_RANK() OVER (ORDER BY salary DESC) in a CTE and filter on the rank you need. Add PARTITION BY dept_id for the Nth highest per department.

How do I delete duplicate rows using ROW_NUMBER?

Number the rows of each duplicate group with ROW_NUMBER() OVER (PARTITION BY duplicate_columns ORDER BY id) and delete the rows with a number greater than 1.