Calculate Rank for users based on groups and score

Question

I saw there are numerous question similar, but I have depleted every solution I found. None of them work as expected for me.

My scenario is as following:

Table with user's score (other data stripped, not relevant):

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id      | int(11)      | NO   | MUL | NULL    |                |
| score        | int(11)      | NO   |     | NULL    |                |
| rank         | int(11)      | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

Table with user's (other data stripped, not relevant):

+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| group_id        | int(11)      | NO   | MUL | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+

Table with groups (other data stripped, not relevant):

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| name         | varchar(100) | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

Requirement is:

Calculate rank based on score for 100 users regardless of group, and increment rank accordingly.
Calculate rank based on score for 100 users based on group (each set of result for each group should have it's own rank), and increment rank accordingly.

Desired result is:

+----+------------+----------+------+
| id |    user_id |    score | rank |
+----+------------+----------+------+
|  2 |         29 |       10 |    1 |
|  5 |         32 |        3 |    2 |
|  6 |         33 |        2 |    3 |
|  7 |         34 |        0 |    4 |
|  9 |         39 |        0 |    5 |
| 11 |         41 |        0 |    6 |
| 15 |         47 |        0 |    7 |
| 18 |         51 |        0 |    8 |
+----+------------+----------+------+

Basically the users should be ranked by score, and the rank should continue until 100 even if they have 0.

Solutions tested:

And more, but the result is the same:

+----+------------+----------+------+
| id |    user_id |    score | rank |
+----+------------+----------+------+
|  2 |         29 |       10 |    1 |
|  5 |         32 |        3 |    2 |
|  6 |         33 |        2 |    3 |
|  7 |         34 |        0 |    4 |
|  9 |         39 |        0 |    4 | <-- NOT OK (should be 5)
| 11 |         41 |        0 |    4 | <-- NOT OK (should be 6)
+----+------------+----------+------+

Notice that some users with score 0 get a rank... "4" and it stops there. I tried to fix this quite a long time but no success.

Haven't even got to the point to calculate per group properly.

Latest query used:

SELECT
    T1.id,
    T1.`user_id`,
    T1.`score`,
    T2.rank
    FROM
    `user_scores` T1
    LEFT JOIN (
    SELECT
        `score`,
        (@v_id := @v_Id + 1) AS rank
    FROM
        (
            SELECT DISTINCT
                `score`
            FROM
                `user_scores`
            ORDER BY
                `score` DESC
        ) t,
        (SELECT @v_id := 0) r
    ) T2 ON T1.`score` = T2.`score`   
    ORDER BY
    `score` DESC
    LIMIT 100

I hope my question is clear enough and that someone can provide me a solution/explanation.

Thanks

@Strawberry What are you talking about ? I provided everything, from table to query. — Mecanik, Jan 22 '20 at 08:30
I can only refer you again to the accepted answer at the linked question — Strawberry, Jan 22 '20 at 09:09

Bluetree · Answer 1 · 2020-01-22T10:27:16.627

Calculate rank based on score for 100 users regardless of group, and increment rank accordingly

SET @v_id:=0;
SELECT T1.id,
       T1.`user_id`,
       T1.`score`,
       (@v_id := @v_Id + 1) AS rank
FROM `user_scores` T1
ORDER BY `score` DESC
LIMIT 100;

Calculate rank based on score for 100 users based on group (each set of result for each group should have it's own rank), and increment rank accordingly.

SET @v_id:=0;


SET @prev:="";


SELECT id,
       user_id,
       group_id,
       score,
       rank
FROM
  (SELECT T.*,
          @prev AS prev,
          (@v_id := (CASE WHEN(@prev = ""
                               OR @prev = T.`group_id`) THEN @v_id + 1
                         ELSE
                                (SELECT @prev := 1)
                     END)) AS rank, @prev := T.`group_id`
   FROM
     (SELECT T1.id,
             T1.`user_id`,
             T1.`score`,
             T2.`group_id`
      FROM `user_scores` T1
      LEFT JOIN `users` T2 ON T2.`id` = T1.`user_id`
      ORDER BY `group_id` DESC, `score` DESC
      LIMIT 100) T) TT;

For number 2, I just saved the previous group_id value and compare it to the current group_id so I will know when to reset the rank.

I updated my requirement, as I do not want to ignore those with 0 anymore. — Mecanik, Jan 22 '20 at 08:49
@NorbertBoros Is it okay for you to usethe `SET @v_id:=0;` outside of `select`? — Bluetree, Jan 22 '20 at 09:02
I can use anything, it doesn't matter as long as I get the result. I read about ROW_NUMBER and it seems to do what I need but need to test. — Mecanik, Jan 22 '20 at 09:16

PrakashT · Answer 2 · 2020-01-22T09:18:14.150

Try this solution.

SELECT
    T1.id,
    T1.`user_id`,
    T1.`score`,
    (@v_id := @v_Id + 1) AS rank
    FROM
    `user_scores` T1,
    (SELECT @v_id := 0) r
    LEFT JOIN (
    SELECT
        `score`,
    FROM
        (
            SELECT
                `score`
            FROM
                `user_scores`
            ORDER BY
                `score` DESC
        ) t,
    ) T2 ON T1.`score` = T2.`score`   
    ORDER BY
    `score` DESC
    LIMIT 100

Second method:

SET @v_id := 0  //first execute this query

/* then execute this query */
SELECT
T1.id,
T1.`user_id`,
T1.`score`,
(@v_id := @v_Id + 1) AS rank
FROM
`user_scores` T1,
LEFT JOIN (
SELECT
    `score`,
FROM
    (
        SELECT
            `score`
        FROM
            `user_scores`
        ORDER BY
            `score` DESC
    ) t,
) T2 ON T1.`score` = T2.`score`   
ORDER BY
`score` DESC
LIMIT 100

This sql query not tested. This is just an idea. – PrakashT Jan 22 '20 at 09:21 — PrakashT, Jan 22 '20 at 09:21

StackSlave · Answer 3 · 2020-01-22T08:18:00.650

-1

Perhaps you want your query like:

SELECT MIN(id) id, user_id, score, rank FROM user_scores GROUP BY rank LIMIT 100

edited Jan 22 '20 at 08:18

answered Jan 22 '20 at 07:53

StackSlave

10,613
2
18
35

Calculate Rank for users based on groups and score

3 Answers3