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:
id | student_code | student_name | score |
1 | #ID_0001 | Student A | 78 |
2 | #ID_0002 | Student B | 80 |
3 | #ID_0003 | Student C | 67 |
4 | #ID_0004 | Student D | 55 |
5 | #ID_0005 | Student E | 78 |
6 | #ID_0006 | Student F | 80 |
7 | #ID_0007 | Student G | 98 |
8 | #ID_0008 | Student H | 67 |
rank_studenty_scores:
id | student_code | student_name | subject | score |
1 | #ID_0001 | Student A | Maths | 68 |
2 | #ID_0002 | Student B | Maths | 75 |
3 | #ID_0003 | Student C | Maths | 75 |
4 | #ID_0004 | Student D | Maths | 59 |
5 | #ID_0005 | Student E | Maths | 58 |
6 | #ID_0006 | Student F | Maths | 79 |
7 | #ID_0007 | Student G | Maths | 68 |
8 | #ID_0008 | Student H | Maths | 90 |
9 | #ID_0001 | Student A | Physics | 98 |
10 | #ID_0002 | Student B | Physics | 95 |
11 | #ID_0003 | Student C | Physics | 95 |
12 | #ID_0004 | Student D | Physics | 75 |
13 | #ID_0005 | Student E | Physics | 75 |
14 | #ID_0006 | Student F | Physics | 65 |
15 | #ID_0007 | Student G | Physics | 70 |
16 | #ID_0008 | Student H | Physics | 65 |
17 | #ID_0001 | Student A | History | 98 |
18 | #ID_0002 | Student B | History | 98 |
19 | #ID_0003 | Student C | History | 98 |
20 | #ID_0004 | Student D | History | 85 |
21 | #ID_0005 | Student E | History | 80 |
22 | #ID_0006 | Student F | History | 90 |
23 | #ID_0007 | Student G | History | 75 |
24 | #ID_0008 | Student H | History | 68 |
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_code | student_name | score | SCORE_RANK |
#ID_0004 | Student D | 55 | 1 |
#ID_0003 | Student C | 67 | 2 |
#ID_0008 | Student H | 67 | 2 |
#ID_0001 | Student A | 78 | 4 |
#ID_0005 | Student E | 78 | 4 |
#ID_0006 | Student F | 80 | 6 |
#ID_0002 | Student B | 80 | 6 |
#ID_0007 | Student G | 98 | 8 |
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_code | student_name | score | SCORE_RANK |
#ID_0007 | Student G | 98 | 1 |
#ID_0006 | Student F | 80 | 2 |
#ID_0002 | Student B | 80 | 2 |
#ID_0001 | Student A | 78 | 4 |
#ID_0005 | Student E | 78 | 4 |
#ID_0003 | Student C | 67 | 6 |
#ID_0008 | Student H | 67 | 6 |
#ID_0004 | Student D | 55 | 8 |
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_code | student_name | subject | score | SCORE_RANK |
#ID_0001 | Student A | Physics | 98 | 1 |
#ID_0001 | Student A | History | 98 | 1 |
#ID_0001 | Student A | Maths | 68 | 5 |
#ID_0002 | Student B | History | 98 | 1 |
#ID_0002 | Student B | Physics | 95 | 2 |
#ID_0002 | Student B | Maths | 75 | 3 |
#ID_0003 | Student C | History | 98 | 1 |
#ID_0003 | Student C | Physics | 95 | 2 |
#ID_0003 | Student C | Maths | 90 | 3 |
#ID_0004 | Student D | Physics | 75 | 4 |
#ID_0004 | Student D | History | 85 | 5 |
#ID_0004 | Student D | Maths | 59 | 7 |
#ID_0005 | Student E | Physics | 75 | 4 |
#ID_0005 | Student E | History | 80 | 6 |
#ID_0005 | Student E | Maths | 58 | 8 |
#ID_0006 | Student F | Maths | 79 | 2 |
#ID_0006 | Student F | History | 90 | 4 |
#ID_0006 | Student F | Physics | 65 | 7 |
#ID_0007 | Student G | Maths | 68 | 5 |
#ID_0007 | Student G | Physics | 70 | 6 |
#ID_0007 | Student G | History | 75 | 7 |
#ID_0008 | Student H | Physics | 90 | 1 |
#ID_0008 | Student H | History | 65 | 7 |
#ID_0008 | Student H | Maths | 68 | 8 |
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_code | student_name | subject | score | SCORE_RANK |
#ID_0001 | Student A | History | 98 | 1 |
#ID_0002 | Student B | History | 98 | 1 |
#ID_0003 | Student C | History | 98 | 1 |
#ID_0006 | Student F | History | 90 | 4 |
#ID_0004 | Student D | History | 85 | 5 |
#ID_0005 | Student E | History | 80 | 6 |
#ID_0007 | Student G | History | 75 | 7 |
#ID_0008 | Student H | History | 68 | 8 |
#ID_0008 | Student H | Maths | 90 | 1 |
#ID_0006 | Student F | Maths | 79 | 2 |
#ID_0002 | Student B | Maths | 75 | 3 |
#ID_0003 | Student C | Maths | 75 | 3 |
#ID_0001 | Student A | Maths | 68 | 5 |
#ID_0007 | Student G | Maths | 68 | 5 |
#ID_0004 | Student D | Maths | 59 | 7 |
#ID_0005 | Student E | Maths | 58 | 8 |
#ID_0001 | Student A | Physics | 98 | 1 |
#ID_0002 | Student B | Physics | 95 | 2 |
#ID_0003 | Student C | Physics | 95 | 2 |
#ID_0004 | Student D | Physics | 75 | 4 |
#ID_0005 | Student E | Physics | 75 | 4 |
#ID_0006 | Student G | Physics | 70 | 6 |
#ID_0007 | Student F | Physics | 65 | 7 |
#ID_0008 | Student H | Physics | 65 | 7 |
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.