Top 2 records in group.

M

Mark Stephenson

I have a table that is set out as show below;

ID Surname First Name TEAM


The only field that will contain duplicated is TEAM. I am wanting to cream
off the top 2 records for each team.

Any ideas?

I tried playing with the IN statement and top 2, but I havnt done this
before and am not really getting anywhere!!!
 
B

Brian Camire

Assuming your table is named "Your Table" and you want the top two in
ascending order of Surname, you might try a query whose SQL looks something
like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[ID] IN
(SELECT TOP 2
[Self].[ID]
FROM
[Your Table] AS [Self]
WHERE
[Self].[TEAM] = [Your Table].[TEAM]
ORDER BY
[Self].[Surname])

This also assumes that values of ID are unique. You may get more than two
records for a TEAM if there are duplicate values of Surname. You will get
fewer than two records if there are fewer than two records for a given TEAM.
 
Top