CODEWITHSUNDEEP
mysqlselectuniquedistinct
quarky
quarky

Reputation: 748

Get top players from the MySQL table

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?

Upvotes: 1

Views: 673

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175646

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

Upvotes: 5

Caffeinated
Caffeinated

Reputation: 12484

This is basic and you should put more effort; here is atemplate you can use - 

SELECT TOP 10 distinct * 
FROM people 
WHERE names='SMITH'
ORDER BY names asc

Upvotes: 0

Related Questions