How to Rank Rows in MySQL Based on Multiple Columns?

Determining Rank Based on Multiple Columns in MySQL

Query:

To rank rows based on multiple columns (user_id and game_id) while considering the descending order of game_detail_sum, you can use a subquery and conditional CASE expressions:

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 

Explanation:

1. The subquery orders the rows in descending order of game_detail_sum for each user_id.
2. The outer query uses user-defined variables @r (row rank) and @u (previous user_id) to assign row numbers.

Improved Query with MySQL 8 Row_Number() Function:

MySQL 8.0 introduces the Row_Number() function, which allows for more efficient row numbering:

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;

Additional Notes:

• In the original approach, using ORDER BY and user variables in the same query may not guarantee correct results due to the optimizer's unpredictable evaluation order.
• The improved query using Row_Number() is more performant and provides predictable results in MySQL 8 .

The above is the detailed content of How to Rank Rows in MySQL Based on Multiple Columns?. For more information, please follow other related articles on the PHP Chinese website!