how to select only max date

[Ben]

Hi all,

I have a table with structure like:

date ID status
09-01-2004 001 active
09-05-2004 001 active
09-10-2004 001 active
09-01-2004 002 active
09-05-2004 002 active
09-10-2004 002 active

I want to pull only the latest date per each ID. So in the example above, I would pull record 3 and 6, because each has the latest date for each of the IDs. How can I do this thru SQL? Thanks for your help.

Ben

 

[Tom Ellison]

Dear Ben:

SELECT [date], ID, status
FROM YourTable T
WHERE [date] = (SELECT MAX([date]) FROM YourTable T1
WHERE T1.ID = T.ID)

If you allow two rows with the same date and ID, then you may get more
than one row for that ID. The query will not arbitrate between them
for you.

Please substitute the actual name of YourTable

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Ben]

Thanks Tom.

Dear Ben:

SELECT [date], ID, status
FROM YourTable T
WHERE [date] = (SELECT MAX([date]) FROM YourTable T1
WHERE T1.ID = T.ID)

If you allow two rows with the same date and ID, then you may get more
than one row for that ID. The query will not arbitrate between them
for you.

Please substitute the actual name of YourTable

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Hi all,

I have a table with structure like:

date ID status
09-01-2004 001 active
09-05-2004 001 active
09-10-2004 001 active
09-01-2004 002 active
09-05-2004 002 active
09-10-2004 002 active

I want to pull only the latest date per each ID. So in the example

above, I would pull record 3 and 6, because each has the latest date for
each of the IDs. How can I do this thru SQL? Thanks for your help.