I have a table called match_score which have following data

Let's just say that round 1 is the preliminary round and 2 is the final round

I want to rank the result based on the score and grouped by the participant , if I'm using some normal sql group by participant and order by score desc.

vlad are the 1st, duff 2nd and gabe 3rd, which one is wrong.

what i want is

1st gabe with 75 point in the final round

2nd john with 70 point in the final round

3rd vlad with 85 point in the preliminary round

4th duff with 80 point in the preliminary round

CodePudding user response：

maybe something like this:

select  h.participant, h.round, h.score 
from    match_score h
where not exists (select 1 from match_score t where t.participant = h.participantand t.round > h.round)
order by h.round desc, h.score desc;