Today I came across the following task while working on a customer project: Take a SQL table "user" with the columns "id" and "score" and determine a ranking of all users sorted by "score", in which users with the same score get the same ranking. By using user-defined variables this task can be solved intuitively and easily.
CREATE TABLE
user(id INT, score INT);
INSERT INTO
user(id, score)
VALUES
(1, 10), (2, 40), (3, 55), (4, 10), (5, 5), (6, 20), (7, 30), (8, 70), (9, 30)
Using two variables solves this simply
SET @score_prev = NULL;
SET @cur_rank = 0;
SELECT id, score, CASE
WHEN @score_prev = score THEN @cur_rank
WHEN @score_prev := score THEN @cur_rank := @cur_rank + 1
END AS rank
FROM user
ORDER BY score DESC
and you get the desired output
+--------+-----------+----------+ | *id* | *score* | *rank* | | 8 | 70 | 1 | | 3 | 55 | 2 | | 2 | 40 | 3 | | 7 | 30 | 4 | | 9 | 30 | 4 | | 6 | 20 | 5 | | 1 | 10 | 6 | | 4 | 10 | 6 | | 5 | 5 | 7 | +--------+-----------+----------+