0

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

TestTable structure

------------------
ID | Name | Score|
------------------
1  | john | 10
2  | mark | 20
3  | ted  | 15
4  | john | 5
5  | ted  | 50
6  | ted  | 70

So when I run

SELECT ID, Name, MAX(Score)
FROM TestTable
GROUP BY Name

I want the query to return entire row for highest score per user.

This is what I am expecting:

2  | mark | 20
1  | john | 10
6  | ted  | 70

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

This is what I am getting:

1  | john | 20
2  | mark | 10
3  | ted  | 70

How can I pull this properly? I am using MySQL.

I need it to perform good as this will be a big query pulling a lot of data from big tables.

Please help :)

0

You need to group by both ID and Name:

SELECT ID, Name, MAX(Score)
FROM TestTable
GROUP BY ID, Name
0

Groupwise Max gives you two ways to solve the problem, depending on whether there can be "duplicates". (That is, can Ted show up twice with the same Score.) Also, having at least version 5.6 is needed in one case.

  • Thank you, I have tried this solution, but the query takes 30+ seconds to execute. 95813 rows in set (31.63 sec) – zachu Jan 26 '16 at 11:31
  • Please add the SELECT to the question; I may be able to spot something to improve. – Rick James Jan 26 '16 at 18:57
0

this answer below worked with me fine in hostgator mySql DB ,Phpmyadmin

SELECT 
OrderedItemBoughtInfo.ItemId,
MAX(OrderedItemBoughtInfo.Date),
OrderedItemBoughtInfo.UnitPrice
FROM
(SELECT 
ItemID, Date, UnitPrice
FROM
ItemBoughtInfo
ORDER BY Date DESC) OrderedItemBoughtInfo
GROUP BY ItemId ;

but in my local mysql server using WorkBench it didn't work with me

  • 2
    This answer may have worked - or appeared to work - but it is not correct for many reasons: Invalid standard SQL, breaks in newer versions, plus you can never be sure that you get correct results. – ypercubeᵀᴹ Apr 29 at 10:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.