Get top players from the MySQL table

Question

I have table in MySQL DB which contains among other things two fields user_id and score. This table is kind of log table so there can be multiple rows for one user_id with different scores. How can I get only top 10 users with highest score from this table?

Answer 1

SELECT DISTINCT user_id
FROM your_table
ORDER BY score DESC
LIMIT 10

EDIT:

SELECT DISTINCT *
FROM your_table
WHERE (user_id, score) IN (SELECT user_id, MAX(score) AS score
                           FROM your_table
                           GROUP BY user_id)
ORDER BY score DESC
LIMIT 10

SqlFiddleDemo