delete duplicate
-
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
-
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:w00t:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
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.
-
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
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply