Need a query that does this...

[0to60]

Consider the following table:

ID dateField intFieldA intFieldB stringFieldA
stringFieldB

I want a query that will give me the row with MAX(dateField) grouped by ID.
If I say:

SELECT Max(dateField), ID
FROM table
GROUP BY ID;

Then that will give me each ID in the table and its maximum dateField. I
want the entire ROW that contains this maximum dateField, and I want it for
each ID. How do I write this query?

 

[Guest]

Do it in 2 seperate queries ( or in a union ).

Part 1 finds the max date and ID, part 2 links the date/ID back to the table
and pulls the row that matches those values.

 

[Douglas J. Steele]

SELECT table.ID, table.dateField, table.intFieldA,
table.intFieldB, table.stringFieldA, table.stringFieldB
FROM table
INNER JOIN
(SELECT Max(DateField), ID
FROM table
GROUP BY ID) AS Sub
ON table.ID = Sub.ID