MySQL | Ranking Functions
The ranking functions in MySQL are used to rank each row of a partition. The ranking functions are also part of MySQL windows functions list.
- These functions are always used with OVER() clause.
- The ranking functions always assign rank on basis of ORDER BY clause.
- The rank is assigned to rows in a sequential manner.
- The assignment of rank to rows always start with 1 for every new partition.
There are 3 types of ranking functions supported in MySQL-
- dense_rank(): This function will assign rank to each row within a partition without gaps. Basically, the ranks are assigned in a consecutive manner i.e if there is a tie between values then they will be assigned the same rank, and next rank value will be one greater than the previous rank assigned.
- rank(): This function will assign rank to each row within a partition with gaps. Here, ranks are assigned in a non-consecutive manner i.e if there is a tie between values then they will be assigned same rank, and next rank value will be previous rank + no of peers(duplicates).
- percent_rank(): It returns the percentile rank of a row within a partition that ranges from 0 to 1. It tells the percentage of partition values less than the value in the current row, excluding the highest value.
In order to understand these functions in a better way. Let consider a table “result”–
s_name |
subjects |
mark |
Pratibha |
Maths |
100 |
Ankita |
Science |
80 |
Swarna |
English |
100 |
Ankita |
Maths |
65 |
Pratibha |
Science |
80 |
Swarna |
Science |
50 |
Pratibha |
English |
70 |
Swarna |
Maths |
85 |
Ankita |
English |
90 |
Queries:
- dense_rank() function-
SELECT subjects, s_name, mark, dense_rank()
OVER ( partition by subjects order by mark desc )
AS 'dense_rank' FROM result;
- Output-Explanation-
Here, table is partitioned on the basis of “subjects”.
order by clause is used to arrange rows of each partition in descending order by “mark”.
dense_rank() is used to rank students in each subject.
Note, for science subject there is a tie between Ankita and Pratibha, so they both are assigned same rank. The next rank value is incremented by 1 i.e 2 for Swarna.
Subjects |
Name |
Mark |
Dense_rank |
English |
Swarna |
100 |
1 |
English |
Ankita |
90 |
2 |
English |
Pratibha |
70 |
3 |
Maths |
Pratibha |
100 |
1 |
Maths |
Swarna |
85 |
2 |
Maths |
Ankita |
65 |
3 |
Science |
Ankita |
80 |
1 |
Science |
Pratibha |
80 |
1 |
Science |
Swarna |
50 |
2 |
- rank() function-
SELECT subjects, s_name, mark, rank()
OVER ( partition by subjects order by mark desc )
AS 'rank' FROM result;
- Output-Explanation-
It’s output is similar to dense_rank() function.
Except, that for Science subject in case of a tie between Ankita and Pratibha, the next rank value is incremented by 2 i.e 3 for Swarna.
Subjects |
Name |
Mark |
rank |
English |
Swarna |
100 |
1 |
English |
Ankita |
90 |
2 |
English |
Pratibha |
70 |
3 |
Maths |
Pratibha |
100 |
1 |
Maths |
Swarna |
85 |
2 |
Maths |
Ankita |
65 |
3 |
Science |
Ankita |
80 |
1 |
Science |
Pratibha |
80 |
1 |
Science |
Swarna |
50 |
3 |
- percent_rank() function-
SELECT subjects, s_name, mark, percent_rank()
OVER ( partition by subjects order by mark )
AS 'percent_rank' FROM result;
- Output-Explanation:
Here, the percent_rank() function calculate percentile rank in ascending order by “mark” column.
percent_rank is calculated using following formula-
(rank - 1) / (rows - 1)
rank is the rank of each row of the partition resulted using rank() function.
rows represent the no of rows in that partition.
To clear this formula, consider following query-
SELECT subjects, s_name, mark, rank()
OVER ( partition by subjects order by mark )-1
AS 'rank-1', count(*) over (partition by subjects)-1
AS 'total_rows-1', percent_rank()
OVER ( partition by subjects order by mark ) AS 'percenr_rank'
FROM result;
- Output-
Subjects |
Name |
Mark |
rank-1 |
total_rows-1 |
percent_rank |
English |
Pratibha |
70 |
0 |
2 |
0 |
English |
Ankita |
90 |
1 |
2 |
0.5 |
English |
Swarna |
100 |
2 |
2 |
1 |
Maths |
Ankita |
65 |
0 |
2 |
0 |
Maths |
Swarna |
85 |
1 |
2 |
0.5 |
Maths |
Pratibha |
100 |
2 |
2 |
1 |
Science |
Swarna |
50 |
0 |
2 |
0 |
Science |
Ankita |
80 |
1 |
2 |
0.5 |
Science |
Pratibha |
80 |
1 |
2 |
0.5 |
Subjects |
Name |
Mark |
percent_rank |
English |
Pratibha |
70 |
0 |
English |
Ankita |
90 |
0.5 |
English |
Swarna |
100 |
1 |
Maths |
Ankita |
65 |
0 |
Maths |
Swarna |
85 |
0.5 |
Maths |
Pratibha |
100 |
1 |
Science |
Swarna |
50 |
0 |
Science |
Pratibha |
80 |
0.5 |
Science |
Ankita |
80 |
0.5 |
Last Updated :
02 Feb, 2023
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...