Remove non-unique ids rows

Question

From a database backup I have records without unique Ids.

Some records have unique IDs. Some records with duplicated IDs contains different DateCreated values. Some records with duplicated IDs contains the same DateCreated values.

I'm trying to get a MSSql 2005 query will leave only unique ID values with the most recent DateCreated value.

From

ID|    DateCreated  
1 |    1/1/09
2 |    1/2/09
2 |    2/2/09
3 |    1/3/09
3 |    1/3/09

To

ID|    DateCreated  
1 |    1/1/09
2 |    2/2/09
3 |    1/3/09

Help

Answer 1

DELETE FROM myTable AS t1 
WHERE EXISTS (
    SELECT 1 FROM myTable AS t2 
    WHERE t1.ID=t2.ID AND t1.DateCreated<t2.DateCreated)

ie, delete any row where there is another row with the same id and a later creation date.

Answer 2

create table #t ( id int, date datetime )

insert #t 
values(1, getdate())

insert #t 
values(1, getdate()+1)

insert #t 
values(1, getdate()-1)

insert #t 
values(2, getdate())

insert #t 
values(2, getdate()+1)

delete t 
from #t t
left join (select id, min(date) as date from #t group by id) as t1 
    on t.id = t1.id and t1.date = t.date
where t1.date is null