Top 2 records in group.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!!!
 
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.
 
Back
Top