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

    Output-

    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

    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.

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

    Output-



    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

    Explanation-

    It’s ouput 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.

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

    Output-

    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

    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

  4. Note: While using ranking function, in MySQL query the use of order by clause is must otherwise all rows are considered as peers i.e(duplicates) and all rows are assigned same rank i.e 1.



    My Personal Notes arrow_drop_up

    Check out this Author's contributed articles.

    If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

    Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




    Article Tags :
    Practice Tags :


    1


    Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.