Select multiple tables with only unique users and ordered by latest id-CodePudding

I have 2 tables, first one is called members:

id  name   show
1   John   1
2   Wil    1
3   George 1
4   Chris  1

Second is called score:

id  user_id  score
1   1        90
2   1        70
3   2        55
4   3        30
5   3        40
6   3        100
7   4        30

user_id from score is the id of members. What I want is to show a scorelist with unique members.id, ordered by score.score and order by the latest score.id.

I use the following code:

SELECT members.id, members.show, score.id, score.user_id, score.score FROM members
INNER JOIN score ON score.user_id = members.id 
WHERE members.show = '1' 
GROUP BY score.user_id
ORDER BY score.score DESC, score.id DESC

The output is not ordered by the latest score.id, but it does show only unique user_id's:

id  user_id  score
1   1        90
3   2        55
4   3        30
7   4        30

It should be like:

id  user_id  score
6   3        100
2   1        70
3   2        55
7   4        30

I hope you can help me

CodePudding user response：

You could use:

with cte as (
              select id,
                    user_id,
                    score,
                    row_number() over(partition by user_id order by id desc) as row_num
               from score     

) select cte.id,user_id,score
  from cte
  inner join members m on cte.user_id=m.id
  where row_num=1
  order by score desc;

Demo

If your MySQL server doesn't support windows function, use:

select s.id,s.user_id,s.score
from score s
inner join members m on s.user_id=m.id
where s.id in (select max(id) as id 
               from score
               group by user_id
               ) 
               
order by score desc;

Demo