I need help with a sub query

here is a look at the data in the table:

ID Status Date
-- --- ----
1 A 2/10/04
1 I 2/11/04
1 A 2/23/04
2 A 2/11/04
2 I 2/13/04
2 A 2/14/04
...

...you get the idea.

I need to get a result set that has one row for each ID. I need the row that has the greatest date. For example, I need a result set that contains:

ID Status Date
-- --- ----
1 A 2/23/04
2 A 2/14/04

It seem simple but yet so hard. Any help would be appreciated. TIACheck out the GROUP BY clause. Your query will look something like this:

SELECT ID, MAX(StatusDate) FROM myTable GROUP BY ID

Don|||Please refer to my post above as to the result set that is needed.|||It is hard to read the example table. There are 3 columns: ID, Status, Date

I need the result set to contain all three columns.

Wes|||Here ya go:

 select A.[ID] as [ID],
 (select Status
 from #MyTable B
 where B.[ID] = A.[ID]
 and B.[Date] = max(A.[Date])) as Status,
 max(A.[Date]) as [Date]
 from #MyTable A
group by [ID]

Enjoy.|||I was so close to that...I did not have the max() in the WHERE clause of the sub query.
THANKS!