MAX, GROUP BY, ORDER in Postgres.
It sounds simple enough but I can't nut out a solution :(
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