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)
