Selecting last two instances of a group

[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

 

[IanH]

There are a few ways you could do this. For example, by using VBA and creating a recordset to populate a table or, creating a table, indexing it and then joining it to itself (but this would have to be recreated and reindexed every time.

There is however a way of doing this in a query. It looks a bit clunky but it works.

Basically you select the records where they match against the name and maximum date for each person. You then use a UNION query to select a second set of data where the maximum date is less than the maximum date of your first part of the query. The code for this is ...


SELECT T.Name, T.Date, T.Amount
FROM DataTable T INNER JOIN

(SELECT Name, Max(Date) AS Max_Date
FROM DataTable
GROUP BY DataTable.Name) T2 ON T.Name = T2.Name AND T.Date = T2.Max_Date


UNION ALL SELECT T.Name, T.Date, T.Amount
FROM DataTable T INNER JOIN

(SELECT T.Name, Max(T.Date) AS Max_Date2
FROM DataTable T INNER JOIN

(SELECT Name, Max(Date) AS Max_Date
FROM DataTable
GROUP BY DataTable.Name) T2 ON T.Name = T2.Name AND T.Date < T2.Max_Date

GROUP BY T.Name) T3 ON T.Name = T3.Name AND T.Date = T3.Max_Date2
ORDER BY Name, Date;


Like I say it looks a bit clunky but works.

However, this is only good if you have one record for each day - if you have multiple records for each day, you will get more than two results for each person.

best regards

Ian

 

[TJBernard]

It worked for me.

Thank you again for your help. This board has done alot to save me through the years, and it comes through again.

Thank you,

TJBernard