MySQL RANK() Function with Examples

How to Use the MySQL RANK() Function

In this tutorial, we'll learn how to use the MySQL RANK() function. The RANK function returns the rank of the current record within the partition of a result of set.

MySQL RANK() Syntax

The syntax of the RANK() function is as follows:

RANK() OVER(
PARTITION BY expression [, expression], ...
ORDER BY expression [ASC | DESC], [, expression], ...
)

Examples

Below are the rank_studentx_scores and rank_studenty_scores:

rank_studentx_scores:

idstudent_codestudent_namescore
1#ID_0001Student A78
2#ID_0002Student B80
3#ID_0003Student C67
4#ID_0004Student D55
5#ID_0005Student E78
6#ID_0006Student F80
7#ID_0007Student G98
8#ID_0008Student H67

rank_studenty_scores:

idstudent_codestudent_namesubjectscore
1#ID_0001Student AMaths68
2#ID_0002Student BMaths75
3#ID_0003Student CMaths75
4#ID_0004Student DMaths59
5#ID_0005Student EMaths58
6#ID_0006Student FMaths79
7#ID_0007Student GMaths68
8#ID_0008Student HMaths90
9#ID_0001Student APhysics98
10#ID_0002Student BPhysics95
11#ID_0003Student CPhysics95
12#ID_0004Student DPhysics75
13#ID_0005Student EPhysics75
14#ID_0006Student FPhysics65
15#ID_0007Student GPhysics70
16#ID_0008Student HPhysics65
17#ID_0001Student AHistory98
18#ID_0002Student BHistory98
19#ID_0003Student CHistory98
20#ID_0004Student DHistory85
21#ID_0005Student EHistory80
22#ID_0006Student FHistory90
23#ID_0007Student GHistory75
24#ID_0008Student HHistory68

Example 1

The following statement selects records from the rank_studentx_scores table, sorted by the score columns in ascending order, and assigns a range to each record:

SELECT student_code, student_name, score, RANK() OVER (ORDER BY score) AS SCORE_RANK
FROM rank_studentx_scores
ORDER BY SCORE_RANK;

Result:

student_codestudent_namescoreSCORE_RANK
#ID_0004Student D551
#ID_0003Student C672
#ID_0008Student H672
#ID_0001Student A784
#ID_0005Student E784
#ID_0006Student F806
#ID_0002Student B806
#ID_0007Student G988

Example 2

The following statement selects records from the rank_studentx_scores table, sorted by the score columns in descending order, and assigns a range to each record:

SELECT student_code, student_name, score, RANK() OVER (ORDER BY score DESC) AS SCORE_RANK
FROM rank_studentx_scores
ORDER BY SCORE_RANK;

Result:

student_codestudent_namescoreSCORE_RANK
#ID_0007Student G981
#ID_0006Student F802
#ID_0002Student B802
#ID_0001Student A784
#ID_0005Student E784
#ID_0003Student C676
#ID_0008Student H676
#ID_0004Student D558

Example 3

The following statement selects records from the rank_studenty_scores table, partitioned by the subject column, sorted by the score columns in descending order, and assigns a range to each record:

SELECT student_name, subject, score, RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS SCORE_RANK
FROM rank_studenty_scores
ORDER BY student_name, SCORE_RANK;

Result:

student_codestudent_namesubjectscoreSCORE_RANK
#ID_0001Student APhysics981
#ID_0001Student AHistory981
#ID_0001Student AMaths685
#ID_0002Student BHistory981
#ID_0002Student BPhysics952
#ID_0002Student BMaths753
#ID_0003Student CHistory981
#ID_0003Student CPhysics952
#ID_0003Student CMaths903
#ID_0004Student DPhysics754
#ID_0004Student DHistory855
#ID_0004Student DMaths597
#ID_0005Student EPhysics754
#ID_0005Student EHistory806
#ID_0005Student EMaths588
#ID_0006Student FMaths792
#ID_0006Student FHistory904
#ID_0006Student FPhysics657
#ID_0007Student GMaths685
#ID_0007Student GPhysics706
#ID_0007Student GHistory757
#ID_0008Student HPhysics901
#ID_0008Student HHistory657
#ID_0008Student HMaths688

Note: The above records were sorted by the student_name and SCORE_RANK.

Example 4

The following statement selects records from the rank_studenty_scores table, partitioned by the subject column, sorted by the score columns in descending order, and assigns a range to each record:

SELECT student_name, subject, score, RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS SCORE_RANK
FROM rank_studenty_scores
ORDER BY subject, SCORE_RANK;

Result:

student_codestudent_namesubjectscoreSCORE_RANK
#ID_0001Student AHistory981
#ID_0002Student BHistory981
#ID_0003Student CHistory981
#ID_0006Student FHistory904
#ID_0004Student DHistory855
#ID_0005Student EHistory806
#ID_0007Student GHistory757
#ID_0008Student HHistory688
#ID_0008Student HMaths901
#ID_0006Student FMaths792
#ID_0002Student BMaths753
#ID_0003Student CMaths753
#ID_0001Student AMaths685
#ID_0007Student GMaths685
#ID_0004Student DMaths597
#ID_0005Student EMaths588
#ID_0001Student APhysics981
#ID_0002Student BPhysics952
#ID_0003Student CPhysics952
#ID_0004Student DPhysics754
#ID_0005Student EPhysics754
#ID_0006Student GPhysics706
#ID_0007Student FPhysics657
#ID_0008Student HPhysics657

Note: The above records were sorted by the subject and SCORE_RANK.

In this tutorial, we've learned how to use the MySQL RANK() function.

See also:
MySQL LIKE Operator Pattern Matching and Examples
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function | Concatenate Strings in MySQL

Leave a Comment