MySQL RANK(), DENSE_RANK(), ROW_NUMBER() and NTILE()

MySQL ranking window functions keep every result row and add ranking information. They use an OVER() clause to define ordering and optional partitions.

RANK() OVER (ORDER BY expression)

Using student sample table.

SELECT id, name, class, mark, gender,
       RANK() OVER (ORDER BY mark) AS rank_no
FROM student
WHERE class = 'Four';

id	name	class	mark	gender	r1
6	Alex John	Four	55	male	1
10	Big John	Four	55	female	1
4	Krish Star	Four	60	female	3
5	John Mike	Four	60	female	3
21	Babby John	Four	69	female	5
1	John Deo	Four	75	female	6
15	Tade Row	Four	88	male	7
16	Gimmy	Four	88	male	7
31	Marry Toeey	Four	88	male	7

Note : OVER() , Partition, RANK() are available in MySQL 8.0 and later.

RANK() leaves gaps after ties. In this result, tied marks share the same rank and the next rank skips ahead.

DENSE_RANK()

When rows tie on the ordering value, RANK() leaves gaps after the tie, while DENSE_RANK() does not.
We will use DENSE_RANK with RANK and ROW_NUMBER for comparison.

SELECT id, name, class, mark, gender,
       ROW_NUMBER() OVER (ORDER BY mark, id) AS row_no,
       RANK() OVER (ORDER BY mark) AS rank_no,
       DENSE_RANK() OVER (ORDER BY mark) AS dense_rank_no
FROM student
WHERE class = 'Four';

id	name	class	mark	gender	rn	r1	DR
6	Alex John	Four	55	male	1	1	1
10	Big John	Four	55	female	2	1	1
4	Krish Star	Four	60	female	3	3	2
5	John Mike	Four	60	female	4	3	2
21	Babby John	Four	69	female	5	5	3
1	John Deo	Four	75	female	6	6	4
15	Tade Row	Four	88	male	7	7	5
16	Gimmy	Four	88	male	8	7	5
31	Marry Toeey	Four	88	male	9	7	5

In case of DENSE_RANK no rank is skipped in case of tie over rank.

RANK(): There is NO silver medal if there are two gold medals.
DENSE_RANK(): There is silver medal even if there are two gold medals.

If there is no duplicate value ( mark ) then there is no difference between RANK() and DENSE_RANK()

ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number starting at 1. For deterministic numbering of tied marks, include a stable tie-breaker such as id in the window ORDER BY clause.

SELECT id, name, class, mark, gender,
       ROW_NUMBER() OVER (ORDER BY mark, id) AS row_no
FROM student
WHERE class = 'Four';

Output

id	name	class	mark	gender	rn
6	Alex John	Four	55	male	1
10	Big John	Four	55	female	2
4	Krish Star	Four	60	female	3
5	John Mike	Four	60	female	4
21	Babby John	Four	69	female	5
1	John Deo	Four	75	female	6
15	Tade Row	Four	88	male	7
16	Gimmy	Four	88	male	8
31	Marry Toeey	Four	88	male	9

Using Partition

SELECT id, name, class, mark, gender,
       ROW_NUMBER() OVER (PARTITION BY gender ORDER BY mark, id) AS row_no
FROM student
WHERE class = 'Four';

Output

id	name	class	mark	gender	rn
10	Big John	Four	55	female	1
4	Krish Star	Four	60	female	2
5	John Mike	Four	60	female	3
21	Babby John	Four	69	female	4
1	John Deo	Four	75	female	5
6	Alex John	Four	55	male	1
15	Tade Row	Four	88	male	2
16	Gimmy	Four	88	male	3
31	Marry Toeey	Four	88	male	4

NTILE(n)

We can group the rows (buckets) by using NTILE(n). Here n is a positive integer.

SELECT id, name, class, mark, gender,
       NTILE(3) OVER (ORDER BY mark, id) AS tile_no
FROM student
WHERE class = 'Four';

id	name	class	mark	gender	my_NTILE
6	Alex John	Four	55	male	1
10	Big John	Four	55	female	1
4	Krish Star	Four	60	female	1
5	John Mike	Four	60	female	2
21	Babby John	Four	69	female	2
1	John Deo	Four	75	female	2
15	Tade Row	Four	88	male	3
16	Gimmy	Four	88	male	3
31	Marry Toeey	Four	88	male	3

Over & partition window LEAD() LAG() Sum Multiple column

All Window Functions LEAD() / LAG() ORDER BY

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Ranking Window Functions

DENSE_RANK()

ROW_NUMBER()

NTILE(n)

Subhendu Mohapatra