sql server programming: Need a query that does this... -- sql server programming

Need a query that does this...

0to60
11/2/2007 2:00:55 PM

sql server programming: 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?

Re: Need a query that does this...

Russell Fields
11/2/2007 3:19:51 PM

0to60,

There is more than one way to write it, so here is one:

SELECT Table.ID, Table.dateField, Table.intFieldA,
Table.intFieldB, Table.stringFieldA, Table.stringFieldB
FROM Table JOIN
(SELECT Max(dateField) MaxDate, ID
FROM table
GROUP BY ID) AS MaxTable
ON Table.ID = MaxTable.ID
AND Table.dateField = MaxTable.MaxDate

RLF

[quoted text, click to view]