Inserting the Most Recent instance

[Raptor136]

Hi all,
I need to insert the most recent instance of a record into another table. Please see below. Thanks.

Fld1 Fld2 Fld3
AAA 10/1/01 Pending
AAA 9/30/01 Received
BBB 11/01/01 Sent
CCC 12/1/01 Sent
CCC 1/1/02 Lost
CCC 7/1/02 Found

I would need to insert the 1st instance of Fld1 = AAA, the only instance of Fld1 = BBB, and the 3rd instance of Fld1 = CCC into another table. I did this once before, but forgot.
Thanks.

Phil raptor136@adelphia.net

 

[tlbroadbent]

Insert table2
Select * from table1 t1
Where fld2 = (Select max(fld2) From table1 Where fld1=t1.fld1) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.