CODEWITHSUNDEEP
sqlmysqldatabase
Kenzie
Kenzie

Reputation: 1285

Fetch one row per account id from list

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!

Upvotes: 6

Views: 6271

Answers (6)

Danimal
Danimal

Reputation: 7710

Try this:

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

Upvotes: 2

orbitaudio
orbitaudio

Reputation:

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.

Upvotes: 0

John Fiala
John Fiala

Reputation: 4581

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.

Upvotes: 1

Eclipse
Eclipse

Reputation: 45533

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

Upvotes: 2

zigdon
zigdon

Reputation: 15073

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

Upvotes: 4

Neall
Neall

Reputation: 27144

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.

Upvotes: 1

Related Questions