MySQL - Ranking Functions

From MySQL | Ranking Functions - GeeksforGeeks

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-

1. 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.
2. 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).
3. 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:

1. dense_rank() function-

SELECT subjects, s_name, mark, dense_rank() 
OVER ( partition by subjects order by mark desc ) 
AS 'dense_rank' FROM result;

1. 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

1. rank() function-

SELECT subjects, s_name, mark, rank() 
OVER ( partition by subjects order by mark desc ) 
AS 'rank' FROM result;

1. 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

1. percent_rank() function-

SELECT subjects, s_name, mark, percent_rank() 
OVER ( partition by subjects order by mark ) 
AS 'percent_rank' FROM result;

1. 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;

1. 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