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

Subscribe to our YouTube Channel here

over() with rank() using MySQL table

DENSE_RANK()

ROW_NUMBER()

NTITLE(n)

Subscribe