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....
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
SELECT
user,
max(score)
FROM
testing
GROUP BY
user
this should do it ..
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
