Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

MySQL | Ranking Functions

  • Last Updated : 20 May, 2019

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-

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

  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_namesubjectsmark
PratibhaMaths100
AnkitaScience80
SwarnaEnglish100
AnkitaMaths65
PratibhaScience80
SwarnaScience50
PratibhaEnglish70
SwarnaMaths85
AnkitaEnglish90

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-

    SubjectsNameMarkDense_rank
    EnglishSwarna1001
    EnglishAnkita902
    EnglishPratibha703
    MathsPratibha1001
    MathsSwarna852
    MathsAnkita653
    ScienceAnkita801
    SciencePratibha801
    ScienceSwarna502

    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-

    SubjectsNameMarkrank
    EnglishSwarna1001
    EnglishAnkita902
    EnglishPratibha703
    MathsPratibha1001
    MathsSwarna852
    MathsAnkita653
    ScienceAnkita801
    SciencePratibha801
    ScienceSwarna503

    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.

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

    Output-



    SubjectsNameMarkpercent_rank
    EnglishPratibha700
    EnglishAnkita900.5
    EnglishSwarna1001
    MathsAnkita650
    MathsSwarna850.5
    MathsPratibha1001
    ScienceSwarna500
    SciencePratibha800.5
    ScienceAnkita800.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-

    SubjectsNameMarkrank-1total_rows-1percent_rank
    EnglishPratibha70020
    EnglishAnkita90120.5
    EnglishSwarna100221
    MathsAnkita65020
    MathsSwarna85120.5
    MathsPratibha100221
    ScienceSwarna50020
    ScienceAnkita80120.5
    SciencePratibha80120.5

    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
Recommended Articles
Page :

Start Your Coding Journey Now!