Selecting values grouped to a specific identifer

Question

I have an application that tracks high scores in a game.

I have a user_scores table that maps a user_id to a score.

I need to return the 5 highest scores, but only 1 high score for any specific user.

So if user X has the 5 highest scores on a purely numerical basis, I simply return the highest one and then the next 4 user scores.

I have tried to use:

SELECT user_id, score 
FROM user_scores 
ORDER BY score DESC 
GROUP BY user_id 
LIMIT 5

But it seems that MySQL drops any user_id with more than 1 score.

Answer 1

You can't group by without a summary-function (SUM, COUNT, etc.)

The GROUP BY clause says how to group the SUMs or COUNTs.

If you simply want to break the long list into bunches with a common value, that's not SQL. That's what your application has to do.

Answer 2

Can you use the Distinct operator to say

SELECT DISTINCT(user_id), score
FROM user_scores
ORDER BY score DESC
LIMIT 5

didn't test so not sure if that will definitely work

Answer 3

This should work:

SELECT user_id, MAX(score)
FROM user_scores 
GROUP BY user_id 
ORDER BY MAX(score) DESC 
LIMIT 5

Answer 4

Returning only the maximum score for a given user is something like the following.

SELECT user_id, max(score) FROM user_scores
GROUP BY user_id

Answer 5

SELECT user_id, MAX(score) AS score
FROM user_scores 
GROUP BY user_id
ORDER BY score DESC
LIMIT 5

Should do the job for you... though don't forget to create indexes...

Answer 6

I don't know whether it was a lack of caffeine or just brain explosion, but the answers here were so easy.

I actually got it working with this monstrosity:

SELECT s1.user_id, 
    (SELECT score FROM user_scores s2 WHERE s2.user_id = s1.user_id ORDER BY score DESC LIMIT 1) AS score 
FROM user_scores s1         
GROUP BY s1.user_id
ORDER BY s1.score DESC     
LIMIT 5