Fetch one row per account id from list

Question

I have a table with game scores, allowing multiple rows per account id: scores (id, score, accountid). I want a list of the top 10 scorer ids and their scores.

Can you provide an sql statement to select the top 10 scores, but only one score per account id?

Thanks!

Answer 1

select top 10 username, max(score) from usertable group by username order by max(score) desc

Answer 2

PostgreSQL has the DISTINCT ON clause, that works this way:

SELECT DISTINCT ON (accountid) id, score, accountid
FROM scoretable
ORDER BY score DESC
LIMIT 10;

I don't think it's standard SQL though, so expect other databases to do it differently.

Answer 3

select username, max(score) from usertable group by username order by max(score) desc limit 10;

Answer 4

First limit the selection to the highest score for each account id. Then take the top ten scores.

SELECT TOP 10 AccountId, Score
FROM Scores s1
WHERE AccountId NOT IN 
    (SELECT AccountId s2 FROM Scores 
     WHERE s1.AccountId = s2.AccountId and s1.Score > s2.Score)
ORDER BY Score DESC

Answer 5

SELECT accountid, MAX(score) as top_score
FROM Scores
GROUP BY accountid,
ORDER BY top_score DESC
LIMIT 0, 10

That should work fine in mysql. It's possible you may need to use 'ORDER BY MAX(score) DESC' instead of that order by - I don't have my SQL reference on hand.

Answer 6

I believe that PostgreSQL (at least 8.3) will require that the DISTINCT ON expressions must match initial ORDER BY expressions. I.E. you can't use DISTINCT ON (accountid) when you have ORDER BY score DESC. To fix this, add it into the ORDER BY:

SELECT DISTINCT ON (accountid) *
FROM scoretable
ORDER BY accountid, score DESC
LIMIT 10;

Using this method allows you to select all the columns in a table. It will only return 1 row per accountid even if there are duplicate 'max' values for score.

This was useful for me, as I was not finding the maximum score (which is easy to do with the max() function) but for the most recent time a score was entered for an accountid.