PHP SQL order by multiple rows-CodePudding

I have a table called 'scorelist' with the following results:

ID   USER_ID   SCORE   SEASON
-----------------------------
1    1         35      3
2    1         45      2
3    2         80      3
4    2         85      1
5    3         65      2

I want to make a score list where I show the scores of the users but only of their last played season. Result should be:

ID   USER_ID   SCORE   SEASON
-----------------------------
3    2         80      3
5    3         65      2
1    1         35      2

I use the following code:

SELECT * FROM scorelist 
WHERE season = (
    SELECT season FROM scorelist ORDER BY season DESC LIMIT 1
) 
GROUP BY user_id 
ORDER BY score DESC;

But then I only get the results of season 3, so a lot of users are not shown.

I also tried:

SELECT * FROM scorelist group by user_id ORDER BY score DESC, season DESC

But this is also not working.
I hope you can help me.

CodePudding user response：

The subquery gets the latest season for each user. If you join to that you get your desired results

SELECT s1.* 
FROM scorelist s1
JOIN 
(
   SELECT user_id, max(season) AS season
   FROM scorelist
   GROUP BY user_id
) s2 ON s1.user_id = s2.user_id AND s1.season = s2.season

CodePudding user response：

Since MySQL 8.0 you can use window function row_number to solve this problem:

WITH ordered_scorelist AS (
    SELECT
        scorelist.*, 
        row_number() over (partition by USER_ID order by SEASON DESC) rn 
    FROM scorelist
) SELECT USER_ID, SCORE, SEASON FROM ordered_scorelist WHERE rn = 1 ;

MySQL row_number test