Selecting the last two instances per group

  • Thread starter Thread starter TJBernard
  • Start date Start date
T

TJBernard

I have a query that is required to pull in only the last
two (date) instances per a certain user, and I cannot for
the life of me figure it out.

Here is an example of the records.

John Gibson 1/2/2004 $3700
John Gibson 2/2/2004 $3500
John Gibson 3/2/2004 $3900
Brad Miller 1/2/2004 $2500
Brad Miller 2/2/2004 $2900
Brad Miller 3/2/2004 $2700

We only need the last two records per user name, so from
this table of data I need to figure out how to select out
this information:

John Gibson 2/2/2004 $3500
John Gibson 3/2/2004 $3900
Brad Miller 2/2/2004 $2900
Brad Miller 3/2/2004 $2700

If anyone has any advice it will be greatly appreciated.

Thank you for your help.

TJBernard
 
One way might be a query whose SQL looks something like this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Your Date Field] In
(SELECT TOP 2
[Self].[Your Date Field]
FROM
[Your Table] AS [Self]
WHERE
[Self].[User Name] = [Your Table].[User Name]
ORDER BY
[Self].[Your Date Field] DESC)
 
Back
Top