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';