Does anyone know how to do a mysql query to select the row in a table with the maximum value? So for instance, I have a table with Name - Value - Data Joe - 1 - SomeData1 Joe - 2 - SomeData2 Bob - 1 - SomeData1 Bob - 2 - SomeData2 Steve - 1 - SomeData1 Steve - 3 - SomeData3 I want to select so the results are: Name - Value - Data Joe - 2 - SomeData2 Bob - 2 - SomeData2 Steve - 3 - SomeData3 I could just use a GROUP BY and the MAX function, except that the data field is not the same for all rows. Thanks in advance for your help!
not sure if this would work but worth a shot. select name, max(value), data from table group by name, data Code (markup):
Close, but not quite: select name,value, data from table group by name having max(value) Code (markup): Hope this helps. Owen
I don't think that will work at all. In fact, it just doesn't on my MySQL installation. What you'd have to do is select the max value, and then query for that row again. What I'm saying is correct. See how MySQL.com does it: http://dev.mysql.com/doc/refman/4.1/en/example-maximum-row.html
Oops. Right you are... I forgot about that MySQL limitation. You could do an order by desc to get the desired results in one query. Take a look at the comments from that MySQL page for an example. Owen