Pull entire row based on MAX(Column) MySQL

Question

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 :)

Answer 1

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.

• zachu: Hi, I forgot to add that I need a record per user. I have updated my question
• ypercubeᵀᴹ: That changes the question entirely. I suggest you post a new one. Or search the site, there are many similar ones.
• zachu: Thank you I have posted new question dba.stackexchange.com/questions/127055/…

Answer 2

I only have a SQL Server instance available to me, but I believe this syntax will work for MySQL as well.

select tt.ID
 , tt.Name
 , tt.Score
from TestTable tt
INNER JOIN (select MAX(Score) as MaxScore from TestTable) tt2
ON tt.Score = tt2.MaxScore

Using the above, you grab the top score in an inline view, and then inner join against that, causing only records with a Score value equal to MaxScore to be returned.

Given your example, this will return the top score. Note, if more than 1 record shares the top score, you'll get multiple records back which share that score.

• zachu: Hi, I forgot to add that I need a record per user. I have updated my question