Deleting all records but most recent date

I have a question on MS SQL 2000.  I am trying to put together a query to delete certain records.  The schema is as follows:


examples being:


Some names occur only once, but there are multiple occurances of the same names with different values for each field thereafter.

What I want to do is delete all records except for the record with the most recent date (keeping name as the key).  I want to end up with only one entry for each name.  When I type this out it seems simple, but I can't seem to formulate the right query.

Thanks for the help!
Who is Participating?
lahousdenConnect With a Mentor Commented:
delete t
from Your_Table as t
left join (select [name], max ([date]) max_date
             from Your_Table
             group by [name]) as m on m.[name] = t.[name] and m.max_date = t.[date]
where m.[name] is null
delete from yourtable
 where exists (select name from yourtable as x
                           where =
                                and <
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.