MySQL over() with rank() ROW_NUMBER() DENSE_RANK() NTILE(n) non-aggregate functions

Using non-aggregate function RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE(n) with OVER we can display row details along with these values.

RANK() OVER ([ partition_clause ] order_by)

Using student sample table.

SELECT id, name,class,mark,gender,rank() over(order by mark)  as 'r1'
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 and above only.

Note that there is no rank value 2 and 4 as there are same ranks for 1 and 3 positions.

DENSE_RANK()

There are some students with same marks. The function RANK() keeps gaps ( between ranks ) but DENSE_RANK() returns without gaps (between ranks ).
We will use DENSE_RANK with RANK and ROW_NUMBER for comparison.

SELECT id, name,class,mark,gender,
ROW_NUMBER() OVER(ORDER BY mark) as 'rn',
RANK() OVER(ORDER BY mark)  as 'r1',
DENSE_RANK() OVER(ORDER BY mark) as DR
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()

Incremental numbers starting from 1 over the partition window. In case of tie same incremental ordering is maintained.

SELECT id, name,class,mark,gender,
ROW_NUMBER() OVER(ORDER BY mark) as 'rn'
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) as 'rn'
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

NTITLE(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) as my_NTILE
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

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

over() with rank() using MySQL table

DENSE_RANK()

ROW_NUMBER()

NTITLE(n)

Subhendu Mohapatra