sql - Get top scorer(s) in MySQL

Question

I have simple table:

ID    Score
1     90
2     85
3     96
4     96
5     73

I want to get the top scorer(s) so I used max function:

select max(s.score) as score,
    s.id
from student_score as s

result:

score    id
96       1

The problem is, there are two top scorers, how am I going to get all top scorers?

Answer 1

The subquery gets the maximum score from table student_score which the result will be used to compare on WHERE clause.

SELECT a.*
FROM student_score a
WHERE Score = 
(
    SELECT MAX(Score)
    FROM student_score
)

• See SQLFiddle Demo