SQL SCORE RANKING

Question

I'm working with score ranking on my app for all user score. My problem is I don't know how to return one row for each stud_num.

My query:

SELECT * FROM score WHERE assess_type = 'professional' ORDER BY total_score DESC.

Result:

As you can see I have 3 stud_num and I only want one row per stud_num and the highest score of it.

Answer 1

You can use correlated query like this:

SELECT * FROM score t
WHERE t.assess_type = 'professional' 
      AND t.total_score = (select max(s.total_score) 
                           from score s
                           where t.stud_num = s.stud_num)
group by stud_num