How to calculate rank in MySQL


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)
mm

About Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq, a business dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build business dashboards & reports for your business. Try it for free today!