Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

delete duplicate Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2007 8:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 4, 2013 11:06 AM
Points: 397, Visits: 548
well lets say I have four records in a table:

Name Address Eff_date
A Lane D 25-09-2007
A Lane K 24-06-2006
A Lane P 23-05-2007
A Lane Z 24-08-2007

I would like to remove every record for A except one with max date. What would the sql look like?
thanks
Post #403032
Posted Wednesday, September 26, 2007 8:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,550, Visits: 2,232
Hi ,

I would prob create a working table to start the ID's of the records witht he Max dates for each change in the name.

I would then delete from the Main table where the ID's are not in the Working table.

Does this make sense?



----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #403039
Posted Wednesday, September 26, 2007 9:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 5,676, Visits: 6,488
The easiest way is to join the table to itself.

Delete from MyTable
from MyTable t1
left outer join (Select Distinct Name, Max(Eff_Date)
From MyTable
Group By Name) t2
on t1.Name = t2.Name
Where t1.Eff_Date <> t2.Eff_Date

Basically, the WHERE clause says "Delete it all where it doesn't match the JOIN conditions above".

Or something similar to that. You'll want to play with this in a Dev environment first before you deploy it to production. Especially as you're deleting data.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #403073
Posted Wednesday, September 26, 2007 9:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:40 PM
Points: 1,683, Visits: 19,609
Try this


with cte
as (select row_number() over(partition by name order by Eff_date desc) as rn
from mytable)
delete from cte
where rn>1



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #403106
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse