how to select only max date

  • Thread starter Thread starter Ben
  • Start date Start date
B

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
 
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
 
Thanks Tom.


Tom Ellison said:
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
Click to expand...
Click to expand...
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.
 
Back
Top