If then-Query

[NeedExcelHelp07]

I have a table that has Columns with the names Status, ID, and Time Stamp.
I want to make a query that if the Id's are a duplicate but the Status are
different, I want whichever time stamp is most recent for the status to
display.

Basically I'm trying to find out the most recent status per user. The way to
find this out is that each row has a time stamp column which identifies when
the status was changed.

Thanks!

 

[Ken Sheridan]

The trick is to use a correlated subquery to identify the latest Time Stamp
value for each ID and restrict the rows returned by the outer query to those
which match those values:

SELECT *
FROM YourTable AS T1
WHERE [Time Stamp] =
(SELECT MAX([Time Stamp])
FROM YourTable AS T2
WHERE T2.ID = T1.ID);

Note how the aliases T1 and T2 are used to differentiate the two instances
of the table and enable the correlation of the subquery with the outer query.

Ken Sheridan
Stafford, England