SQL - One result per user record

I have a web site that collects high scores for a game - the sidebar shows the latest 10 scores (not necessarily the highest, just the latest 10). However, since a user can play multiple games quickly, they can dominate the latest 10 list. How can I write an SQL squery to show the last 10 scores but limit it开发者_运维技巧 to one per user?

SELECT username, max(score) 
FROM Sometable
GROUP BY username
ORDER BY Max(score) DESC

and from that, select the top X depending on your db platform. select top(10) in ms-sql 2005+

edit

sorry, I see that you want things ordered by date.

Here's a working query with ms-sql 2005.

;
WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY username ORDER BY dateadded DESC) AS 'RowNo',
username, score, dateadded FROM SomeTable
)
SELECT username, score, dateadded FROM CTE
WHERE RowNo = 1 

Group by user... and either select the Max(Score), Max([Submission Date]) or whatever.

In SQL Server, you could use the RANK() OVER() with appropriate PARTITION and GROUP BY, but what platform are you using?

In the interest of providing another point of view you could just add a field "max score" to your user table and then use a simple query with an order by to get the top 10.

Your update will need to check if the new score if higher then the current max score.

It does have the advantage of querying a table that will most probably have less rows then your score table.

Anyway, just another option to consider.

SELECT s2.*

FROM 
 (SELECT user_id, MAX(action_time) AS max_time  
 FROM scores s1 GROUP_BY user_id   
 ORDER BY MAX(action_time) DESC LIMIT 10)s1
INNER JOIN scores s2 ON (s2.user_id = s1.user_id AND s2.action_time = s1.max_time)        

This is Mysql syntax, for SQL server you need to use SELECT TOP 10 ... instead of LIMIT 10.

Here is a working example that I built on SQL Server 2008

WITH MyTable AS
(
    SELECT 1 as UserId, 10 as Score UNION ALL
    SELECT 1 as UserId, 11 as Score UNION ALL
    SELECT 1 as UserId, 12 as Score UNION ALL
    SELECT 2 as UserId, 13 as Score UNION ALL
    SELECT 2 as UserId, 14 as Score UNION ALL
    SELECT 3 as UserId, 15 as Score UNION ALL
    SELECT 3 as UserId, 16 as Score UNION ALL
    SELECT 3 as UserId, 17 as Score UNION ALL
    SELECT 4 as UserId, 18 as Score UNION ALL
    SELECT 4 as UserId, 19 as Score UNION ALL
    SELECT 5 as UserId, 20 as Score UNION ALL
    SELECT 6 as UserId, 21 as Score UNION ALL
    SELECT 7 as UserId, 22 as Score UNION ALL
    SELECT 7 as UserId, 23 as Score UNION ALL
    SELECT 7 as UserId, 24 as Score UNION ALL
    SELECT 8 as UserId, 25 as Score UNION ALL
    SELECT 8 as UserId, 26 as Score UNION ALL
    SELECT 9 as UserId, 26 as Score UNION ALL
    SELECT 10 as UserId, 20 as Score 
),
MyTableNew AS
(
    SELECT Row_Number() OVER (Order By UserId) Sequence, *
    FROM MyTable 
),
RankedUsers AS
(
    SELECT *, Row_Number() OVER (Partition By UserId ORDER BY Sequence DESC) Ranks
    FROM MyTableNew
)
SELECT *
FROM MyTableNew
WHERE Sequence IN
(
    SELECT TOP 5 Sequence
    FROM RankedUsers
    WHERE Ranks = 1
    ORDER BY Sequence DESC
)