Mysql – Pull entire row based on MAX(Column) MySQL

MySQL

I am trying to pull whole record from database for the highest "Score" column.

TestTable structure

------------------
ID | Name | Score|
------------------
1  | john | 10
2  | mark | 20
3  | ted  | 15

So when I run

SELECT ID, Name, MAX(Score)
FROM TestTable

So I want the query to return entire row 2 because this is the highest value.

This is what I am expecting:

2  | mark | 20

But it does not necessarily return correct row. It always return MAX(Score) but other values are not necessarily from the same row.

This is what I am getting:

3  | ted  | 20

Is there a better way of pulling this data?
I am using MySQL.

Also I am not interested in sub-query such as 

SELECT ID, Name, MAX(Score)
FROM TestTable
WHERE ID = (
    SELECT ID From TestTable WHERE Score = MAX(Score);
)

Because I need it for big query using a lot of data, and it needs to perform good.

Please help 🙂

Best Answer

If you don't care about ties and want one row only and always one row, then the following could be used. With an index on (Score), should be as efficient as possible:

SELECT ID, Name, Score
FROM TestTable
ORDER BY Score DESC 
LIMIT 1 ;

If you care about ties but still want only one row, you'll have to decide how ties should be resolved and modify the ORDER BY accordingly. If, for example, the row with the first name in lexicographic order is wanted in case of ties, then use the following - and an index on (Score, Name):

ORDER BY Score DESC, Name ASC

If you want all tied results, then the query by @Dan in the other answer should be the best. The index on (Score) would be sufficient for that.

Related Question