Solved

Deleting all records but most recent date

Posted on 2007-03-27
2
263 Views
Last Modified: 2010-03-20
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:

[name][date][item1][item2]...

examples being:

[andrew][2/2/07][meat][cheese]
[bob][1/1/07][milk][eggs]
[bob][1/2/07][milk][cheese]

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!
0
Comment
Question by:Ant_Dogg
2 Comments
 
LVL 10

Accepted Solution

by:
lahousden earned 50 total points
ID: 18803708
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
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18805748
delete from yourtable
 where exists (select name from yourtable as x
                           where yourtable.name = x.name
                                and yourtable.date < x.date)
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question