Inquisitio SQL determinare a album ranking08
14

Hodie invenimus hoc negotium dum opus in inceptis emptoris: Sume SQL mensam "usoris" cum columnis "id" et "sexaginta" et pone ordinem notarum omnium utentium, digestum secundum "sexaginta", in quo utentes cum eodem titulo eundem ordinem obtinent. Utendo variabilium usorum definitarum, hoc negotium intuitive et facile solvi potest.

---

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)

Per duas tantum variables solvit hanc:

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

et in output ut vos volo:

+--------+-----------+----------+
|  *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   |
+--------+-----------+----------+