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

Is the ordering based on ascending order of game_id or the descending order of game_detail ? — Madhur Bhaiya
– Madhur Bhaiya, Commented Nov 25, 2018 at 6:15

Madhur Bhaiya · Accepted Answer · 2018-11-26 12:50:44Z

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

Gordon Linoff · Accepted Answer · 2018-11-26 12:30:44Z

6

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).

edited Nov 26, 2018 at 12:30

answered Nov 25, 2018 at 21:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Madhur Bhaiya Over a year ago

@lastUserId := user_id can still be evaluated before the if() expression. definitely some trick involved in if(user_id := @lastUserId, but unable to understand so. How does that work ? Some explanation would be handy.

Gordon Linoff Over a year ago

@MadhurBhaiya . . . That line was a mistake and has been removed. It is not used in the calculation.

Madhur Bhaiya Over a year ago

Makes sense now. +1

juergen d · Accepted Answer · 2018-11-25 16:30:42Z

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

edited Nov 25, 2018 at 16:30

answered Nov 25, 2018 at 6:16

juergen d

205k40 gold badges303 silver badges377 bronze badges

6 Comments

Madhur Bhaiya Over a year ago

@GordonLinoff particular blog from MySQL team may be helpful: mysqlserverteam.com/… 2) This answer is wrong because it does not determine the row numbering based on game_detail in descending order. It just seem to work, because (unfortunately) OP's sample data itself is insufficient (it is already sorted). Thirdly, afaik, evaluation of user variables in this and the other answer (mine) is happening in two different expressions (separated by comma). Will be happy if specific difference can be showcased

juergen d Over a year ago

@MadhurBhaiya: I added an order by

Madhur Bhaiya Over a year ago

@juergend I will also ask you to read this blog once: mysqlserverteam.com/… Basically MySQL does not guarantee whether evaluation will happen before order by or after (due to its own optimization kicking in).

Madhur Bhaiya Over a year ago

@juergend I had a chat with Nick about this kind of problems, and unexpected behaviour in certain scenarios etc, in comments of another question. You may be interested in the same: stackoverflow.com/questions/53404473/…

Gordon Linoff Over a year ago

@MadhurBhaiya . . . You're right. This makes the same mistake yours does.

|

Salman Arshad · Accepted Answer · 2018-11-27 06:10:56Z

1

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.

edited Nov 27, 2018 at 6:10

answered Nov 27, 2018 at 5:58

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Comments

AmrDeveloper · Accepted Answer · 2022-11-22 07:55:07Z

0

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

edited Nov 22, 2022 at 7:55

answered Nov 22, 2022 at 7:49

AmrDeveloper

4,8003 gold badges25 silver badges33 bronze badges

Comments

Chris Catignani · Accepted Answer · 2023-10-26 20:02:53Z

0

When you use MySql upper 8.0.0,

SELECT *, RANK() OVER (ORDER BY game_detail DESC) FROM game_logs

edited Oct 26, 2023 at 20:02

Chris Catignani

5,30819 gold badges46 silver badges54 bronze badges

answered Oct 26, 2023 at 7:21

Benjamin Hyo-keun Oh

1

Collectives™ on Stack Overflow

Determine Rank based on Multiple Columns in MySQL

6 Answers 6

Comments

3 Comments

SQLFiddle Demo

6 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

3 Comments

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related