Extract one row from one account id from list

Question

I have a game score table that allows multiple rows per account ID: scores (id, score, accountid) . I want a list of the top 10 identifiers and their counters.

Can you provide a sql statement to select the top 10 points, but only one point per account ID?

Thanks!

Answer 1

First, limit the selection to the highest score for each account ID. Then take the ten.

 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 2

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

Answer 3

Try the following:

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

Answer 4

PostgreSQL has a DISTINCT ON clause that works as follows:

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

I do not consider this standard SQL, so we expect other databases to do this differently.

Answer 5

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

This should work fine in mysql. You may need to use "ORDER BY MAX (grade) DESC" instead of this order - I don't have a SQL reference.

Answer 6

I believe PostgreSQL (at least 8.3) will require that the DISTINCT ON expressions must match the initial ORDER BY expressions. I.E. you cannot use DISTINCT ON (accountid) if you have an ORDER BY score DESC . To fix this, add it to ORDER BY :

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

With this method, you can select all the columns in the table. It will only return 1 row per account, even if there are double "max" values ​​for the score.

This was useful for me, since I did not find the maximum score (which is easy to do with the max () function), but the account was last entered for the account.