How to calculate rank in MySQL

Last updated on September 11th, 2020 at 09:25 am

Many a time you need to calculate rank in MySQL. You can order by a number or text but can’t calculate rank in MySQL easily. Here’s a query you can use. Just replace the table name scores and column names id, score.

Calculate rank in MySQL based on increasing value

Example:

CREATE TABLE score (id int, score int);
INSERT INTO scores VALUES (1, 35),(2, 10),(3,30),(4,22),(5,20),(6,18),(7,36);
Scores:
+----+------+
| id | score|
+----+------+
| 1  |   35 |
| 2  |   10 |
| 3  |   30 |
| 4  |   22 |
| 5  |   20 |
| 6  |   18 |
| 7  |   36 |
+----+------+

We use a ranking variable, such as the following:

SELECT    id,score,
          @curRank := @curRank + 1 AS rank
FROM      scores p, (SELECT @curRank := 0) r
ORDER BY  score;

The

(SELECT @curRank := 0)

part allows the variable initialization without requiring a separate SET command.

Result:
+----+------+------+
| id | score| rank |
+----+------+------+
| 2  |   10 |    1 |
| 6  |   18 |    2 |
| 5  |   20 |    3 |
| 4  |   22 |    4 |
| 3  |   30 |    5 |
| 1  |   35 |    6 |
| 7  |   36 |    7 |
+----+------+------+
7 rows in set (0.02 sec)

Calculate rank in MySQL based on decreasing value

If you want to calculate rank in MySQL based on decreasing order of scores just add the keyword DESC in query.

SELECT    id,score,
          @curRank := @curRank + 1 AS rank
FROM      scores p, (SELECT @curRank := 0) r
ORDER BY  score DESC;

Result:

+----+------+------+
| id | score| rank |
+----+------+------+
| 7  |   36 |    1 |
| 1  |   35 |    2 |
| 3  |   30 |    3 |
| 4  |   22 |    4 |
| 5  |   20 |    5 |
| 6  |   18 |    6 |
| 2  |   10 |    7 |
+----+------+------+
7 rows in set (0.02 sec)