PHP SQL order by multiple rows

Question

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.

I use the following code ща course, you got last season at all, over all users, whereas last season differs for each separate user. I also tried The query have incomplere grouping. — Akina
– Akina, Commented Oct 25, 2021 at 8:04

juergen d · Accepted Answer · 2021-10-25 08:17:11Z

3

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

edited Oct 25, 2021 at 8:17

answered Oct 25, 2021 at 8:00

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Caat Over a year ago

Thank you very much! This was indeed the solution.

Slava Rozhnev · Accepted Answer · 2021-10-25 08:35:13Z

1

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 
ORDER BY SCORE DESC;

MySQL row_number test

answered Oct 25, 2021 at 8:35

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

3 Comments

Caat Over a year ago

Thanks! Is it also possible to use a range of the 3 latest seasons? So that users who have not participated for a long time are not shown.

Slava Rozhnev Over a year ago

Yes, it possible. Look here: sqlize.online/…

Caat Over a year ago

You are the best. It works, thank you!

Collectives™ on Stack Overflow

PHP SQL order by multiple rows

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related