grouping problem in sql

Question

Cant get my head round the sql for a leaderboard part of a facebook app I'm developing. The idea is, a list of peoples friends is passed in the WHERE clause (i.e 2,1,3,4 would be ID's of someones friends) therefore the leaderboard is a list of someones friends including themselves.

I want the top score for every fb_id and want it in descending order- Im still getting lower scores than the maximum for certain fb_id.

SELECT fb_id, score FROM scores WHERE fb_id IN (2,1,3,4) GROUP BY fb_id ORDER BY score DESC;

Answer 1

You could do a:

SELECT fb_id, MAX(score) FROM scores WHERE fb_id IN (2,1,3,4) GROUP BY fb_id

That should do it!

Answer 2

You need to use MAX, and you wouldn't need the ORDER BY clause:

SELECT fb_id, 
MAX(score) AS 'MaxScore'
FROM scores 
WHERE fb_id IN (2,1,3,4) 
GROUP BY fb_id