Top N Records for a Group by

[-PJ]

I'm in Access 2003 and have a table [match] where i have match_id and
player_id. I'm trying to design a query where I return the last 10 matches
for a given player.

I can return the last 10 matches using a top query:

select top 10 match_id from match order by match_id desc

but I've not been able to figure out how to get the top 10 matches for each
player_id. Please assist. Thanks.

 

[Dale Fye]

PJ,

Assuming you have a [MatchDate] field, try something like:

Select T1.Player_ID, T1.Match_ID, Count(T2.Player_ID) as MatchOrder
FROM yourTable as T1
LEFT JOIN yourTable as T2
ON T1.[Player_ID] = T2.[Player_ID]
AND T2.[Match_Date] <= T1.[Match_Date]
WHERE Count(T2.Player_ID) <= 10
GROUP BY T1.Player_ID, T1.Match_ID

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.