How to Rank Users Based on Multiple Columns in MySQL?

Determining Rank Based on Multiple Columns in MySQL

When working with data in MySQL, it is often necessary to determine the rank of rows based on specific criteria. In this case, the task involves ranking users based on both user ID and game ID, with the rankings ordered in descending order of game details.

To address this problem, we will utilize a subquery to order the data such that all rows with the same user ID are grouped together, with further sorting between them based on game details in descending order. Then, we will use conditional CASE..WHEN expressions to evaluate row numbering and assign user game ranks.

The following SQL query provides a solution:

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 

In the query, we use user-defined variables @r and @u to keep track of row rankings and user IDs. We ensure that the evaluation of user_game_rank and user ID assignment to @u occur within the same expression to maintain their correct order.

The result of this query will provide the user game ranks as desired:

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

For MySQL 8.0 and above, a more efficient approach is to use the Row_Number() function, which can directly calculate row rankings based on specified criteria:

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;

This query will provide the same output as the previous one, but it offers improved performance and is the recommended method for row ranking in MySQL 8.0.

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