I have a rounds table (id, user_id, total_score)I'm wanting to get the top 10 scores but group by the users so that the top ten doesn't have any of the users more than once.

This is giving the right scores:SELECT MAX(total_score) as max_score, user_id FROM "rounds" GROUP BY user_id ORDER BY max_score LIMIT 10

I'm wanting to get the round id's though and including that in the group by means that the users aren't unique anymore.SELECT MAX(total_score) as max_score, user_id, id FROM "rounds" GROUP BY user_id, id ORDER BY max_score LIMIT 10

SELECT MAX(total_score) as max_score, user_id, id FROM "rounds" GROUP BY user_id ORDER BY max_score LIMIT 10;ERROR: column "rounds.id" must appear in the GROUP BY clause or be used in an aggregate function

Edit Oh, I think this works.SELECT MAX(total_score) as max_score, user_id, MAX(id) as id FROM "rounds" GROUP BY user_id ORDER BY max_score LIMIT 10;

If I'm not mistaken, that solution will still give you more than 1 row per user, if a user has multiple rows with the same max score.If you only want one row per user returned, and you don't care which one, then your solution is the way to go (returning always the latest round id of the rows with the max score for that user).

The previous one wasn't returning MAX(total_score) anymore, It seemed to be taking the MAX(id) in preference somehow..

Because SQL does my head in and for efficiency I've actually chosen to bake the best_score out and save it against the user.Then it's a much more straightforward SELECT * FROM User ORDER BY max_score DESC LIMIT 10

Are there good rules of thumb for baking out calculated fields like this in the db or is that frowned upon?

If I'm not mistaken, that solution will still give you more than 1 row per user, if a user has multiple rows with the same max score.If you only want one row per user returned, and you don't care which one, then your solution is the way to go (returning always the latest round id of the rows with the max score for that user).

Use a bigger LIMIT for the query extracting both user_id and id, a 100 would cover the worst case scenario, where every top player has top scores, make it a subquery, then group that by user in parent query to have only one player appearing for the top 10 positions.

Thanks, but swampBoogie's answer is correct.In any case, I've changed it now so that when a score is saved if it's the highest score it will be saved directly against the user now.[sql]SELECT * FROM User ORDER BY max_score DESC LIMIT 10[/sql]I think baking out calculations like this make sense and keep things faster / simpler in sql land.