Mysql query for getting unique rows

Question

I have table named 'testing' of 3 columns i.e. user,score,time in that 'time' is unique and user,score is repeated with different score. like below

user |   score  |   time
----------------------------
535  | 17       | 1279170280
535  | 1        | 1279170693
100  | 55       | 1279171361
100  | 30       | 1279171412
535  | 2        | 1279173412
595  | 4        | 1279173539
595  | 22       | 1279173571
595  | 50       | 1279173775

now how do i get high scored users uniquely. please help....

Answer 1

The following will get you a list of the highest score per each user.

select user, max(score) from table group by user

If you want the top 10, just add limit

select user, max(score) from table group by user limit 10

Answer 2

SELECT 
  user,
  max(score)
FROM
 testing
GROUP BY
 user

this should do it ..

Answer 3

select * 
from testing join 
     (select max(score) score, user 
      from testing 
      group by user) high using (score, user)

This should give you the highest score, the user# and the time of that score if that's what you wanted. If you don't need the time, you can execute only the inner query