Query by most recent modified date

[tcb]

A table like this:

moddate ID

1/1/2007 001
1/2/2007 001
1/3/2007 001
1/1/2007 002
1/2/2007 002

What would be the SQL to draw only the most recently modified record
for each ID? Like this:

1/3/2007 001
1/2/2007 002

 

[Allen Browne]

1. Create a query using this table.

2. Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

3. Drag the ID into the grid.
Accept Group By in the total row under this field.

4. Drag moddate into the grid.
Choose Max under this field in the Total row.

 

[Guest]

If there are other columns in the table which you want returned as well as
the ID and moddate then you can use a subquery to restrict the result to the
latest moddate per ID, e.g.

SELECT *
FROM YourTable AS T1
WHERE moddate =
(SELECT MAX(moddate)
FROM YourTable AS T2
WHERE T2.ID = T1.ID);

Ken Sheridan
Stafford, England