CODEWITHSUNDEEP
mysqlsql
kazinix
kazinix

Reputation: 30103

Get top scorer(s) in MySQL

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?

Upvotes: 0

Views: 1297

Answers (4)

roman
roman

Reputation: 117350

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

If you want your subquery to be reusable, try this

select s.score, s.id, m.score
from student_score as s
    cross join (select max(t.score) as score from student_score as t) as m
where s.score = m.score

SQL FIDDLE EXAMPLE

Upvotes: 3

John Woo
John Woo

Reputation: 263713

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
)

Upvotes: 5

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

Use your query as a sub query to select the score from the actual table

select * 
from  student_score 
where score in
 (
    select max(score)
    from   student_score )


SQL Fiddle demo

Upvotes: 2

alexn
alexn

Reputation: 58962

Max is an aggregate function and returns only one row. The easiest would be to issue a subquery:

SELECT * FROM student_score
WHERE score = (
    SELECT MAX(score) FROM student_score
)

Make sure you have an index on score if you have a lot of rows.

Upvotes: 1

Related Questions