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-

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"-

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.

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.

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-