MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE members(
   id int,
   name varchar(20),
   `show` int 
);


insert into members values
(1,'John',1),
(2,'Wil',1),
(3,'George',1),
(4,'Chris',1);

CREATE TABLE score(
   id int,
   user_id int ,
   score int 
);


insert into score values
(1,1,90),
(2,1,70),
(3,2,55),
(4,3,30),
(5,3,40),
(6,3,100),
(7,4,30);

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;


id
user_id
score


6
3
100

2
1
70

3
2
55

7
4
30


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;




id
user_id
score


6
3
100

2
1
70

3
2
55

7
4
30