Determine Rank based on Multiple Columns in MySQL

Question

I have a table which has 3 fields, I want to rank column based on user_id and game_id.

Here is SQL Fiddle : http://sqlfiddle.com/#!9/883e9d/1

the table already I have :

user_id	game_id	game_detial_sum
6	10	1000
6	11	260
7	10	1200
7	11	500
7	12	360
7	13	50

expected output :

user_id	game_id	game_detial_sum	user_game_rank
6	10	1000	1
6	11	260	2
7	10	1200	1
7	11	500	2
7	12	360	3
7	13	50	4

My efforts so far :

SET @s := 0; 
SELECT user_id,game_id,game_detail, 
       CASE WHEN user_id = user_id THEN (@s:=@s+1) 
            ELSE @s = 0 
       END As user_game_rank 
FROM game_logs

Edit: (From OP Comments): Ordering is based on the descending order of game_detail

order of game_detail

Answer 1

In a Derived Table (subquery inside the FROM clause), we order our data such that all the rows having same user_id values come together, with further sorting between them based on game_detail in Descending order.

Now, we use this result-set and use conditional CASE..WHEN expressions to evaluate the row numbering. It will be like a Looping technique (which we use in application code, eg: PHP). We would store the previous row values in the User-defined variables, and then check the current row's value(s) against the previous row. Eventually, we will assign row number accordingly.

Edit: Based on MySQL docs and @Gordon Linoff's observation:

The order of evaluation for expressions involving user variables is undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.

We will need to evaluate row number and assign the user_id value to @u variable within the same expression.

SET @r := 0, @u := 0; 
SELECT
  @r := CASE WHEN @u = dt.user_id 
                  THEN @r + 1
             WHEN @u := dt.user_id /* Notice := instead of = */
                  THEN 1 
        END AS user_game_rank, 
  dt.user_id, 
  dt.game_detail, 
  dt.game_id 

FROM 
( SELECT user_id, game_id, game_detail
  FROM game_logs 
  ORDER BY user_id, game_detail DESC 
) AS dt 

Result

| user_game_rank | user_id | game_detail | game_id |
| -------------- | ------- | ----------- | ------- |
| 1              | 6       | 260         | 11      |
| 2              | 6       | 100         | 10      |
| 1              | 7       | 1200        | 10      |
| 2              | 7       | 500         | 11      |
| 3              | 7       | 260         | 12      |
| 4              | 7       | 50          | 13      |

View on DB Fiddle

---

An interesting note from MySQL Docs, which I discovered recently:

Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.

Also, thanks to a fellow SO member, came across this blog by MySQL Team: https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/

General observation is that using ORDER BY with evaluation of the user variables in the same query block, does not ensure that the values will be correct always. As, MySQL optimizer may come into place and change our presumed order of evaluation.

Best approach to this problem would be to upgrade to MySQL 8+ and utilize the Row_Number() functionality:

Schema (MySQL v8.0)

SELECT user_id, 
       game_id, 
       game_detail, 
       ROW_NUMBER() OVER (PARTITION BY user_id 
                          ORDER BY game_detail DESC) AS user_game_rank 
FROM game_logs 
ORDER BY user_id, user_game_rank;

Result

| user_id | game_id | game_detail | user_game_rank |
| ------- | ------- | ----------- | -------------- |
| 6       | 11      | 260         | 1              |
| 6       | 10      | 100         | 2              |
| 7       | 10      | 1200        | 1              |
| 7       | 11      | 500         | 2              |
| 7       | 12      | 260         | 3              |
| 7       | 13      | 50          | 4              |

View on DB Fiddle

Answer 2

The best solution in MySQL, prior to version 8.0 is the following:

select gl.*, 
       (@rn := if(@lastUserId = user_id, @rn + 1,
                  if(@lastUserId := user_id, 1, 1)
                 )
        ) as user_game_rank
from (select gl.*
      from game_logs gl
      order by gl.user_id, gl.game_detail desc
     ) gl cross join
     (select @rn := 0, @lastUserId := 0) params;

The ordering is done in a subquery. This is required starting around MySQL 5.7. The variable assignments are all in one expression, so different order of evaluation of expressions doesn't matter (and MySQL doesn't guarantee the order of evaluation of expressions).

Answer 3

SELECT user_id, game_id, game_detail, 
       CASE WHEN user_id = @lastUserId 
            THEN @rank := @rank + 1 
            ELSE @rank := 1 
       END As user_game_rank,
       @lastUserId := user_id
FROM game_logs
cross join (select @rank := 0, @lastUserId := 0) r
order by user_id, game_detail desc

SQLFiddle Demo

Answer 4

You can use a very simple correlated sub query:

SELECT *, (
    SELECT COUNT(DISTINCT game_detail) + 1
    FROM game_logs AS x
    WHERE user_id = t.user_id AND game_detail > t.game_detail
) AS user_game_rank
FROM game_logs AS t
ORDER BY user_id, user_game_rank

DB Fiddle

It is slower but far more reliable than user variables. All it takes is one JOIN to break them.

Answer 5

Thanks to Madhur Bhaiya answer, In my use case we expect the rank to be the same when user_id is the same for example,

user_id	game_id	game_detial_sum	user_game_rank
6	10	1000	1
6	11	260	1
7	10	1200	2
7	11	500	2
7	12	360	2
7	13	50	2

The query

SET @rank := 0, @last_user_id := 0; 
SELECT
 @rank := CASE WHEN @last_user_id = dt.user_id 
                 THEN @rank
            WHEN @last_user_id := dt.user_id
                 THEN @rank + 1
       END AS user_game_rank, 
 dt.user_id, 
 dt.game_detail, 
 dt.game_id 

FROM (SELECT user_id, game_id, game_detail
 FROM game_logs 
 ORDER BY user_id, game_detail DESC
) AS dt