Deleting Duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Deleting Duplicates

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2011-09-19 : 11:59:08

Hello

I have a table with many duplicates records that vary only by Activity_Date. I want to keep the most recent Activity_Date record and delete all of the others. I've identified the records I want to keep but don't know how to delete the remaining records.

SELECT
MAX(Activity_Date) AS MaxActivity_Date, Bond_Number
FROM MyTable
GROUP BY Bond_Number

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 12:01:48

[code]DELETE t
FROM MyTable t
LEFT JOIN (SELECT
MAX(Activity_Date) AS MaxActivity_Date, Bond_Number
FROM MyTable
GROUP BY Bond_Number)t1
On t1.Bond_Number = t.Bond_Number
AND t1.MaxActivity_Date = t.Activity_Date
WHERE t1.Bond_Number IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

evanburen
Posting Yak Master

167 Posts

Posted - 2011-09-19 : 13:43:58

Thanks for the response. This actually deletes the record that I want to keep - MAX(Activity_Date). It's the other duplicates that I want to delete. If I have 3 records all with Bond_Number, 1234 and Activity Dates of 09/15/11, 09/15/11 and 9/16/11, I want to keep only the one with Activity Date of 09/16/11 and delete any others with .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 14:39:16

please check the query clearly. it will delete only others keeping max activity date record intact.

see illustration below


create table #testtable 
(
Bond_Number int,
Activity_Date datetime
)

insert #testtable
SELECT 110,'20100512' union all
SELECT 110,'20110902' union all
SELECT 110,'20110302' union all
SELECT 112,'20101202' union all
SELECT 115,'20071223' union all
SELECT 110,'20090512' union all
SELECT 322,'20111118' union all
SELECT 112,'20101108' union all
SELECT 115,'20050602' union all
SELECT 322,'20090101' 

SELECT * FROM #testtable

DELETE t
FROM #testtable t
LEFT JOIN (SELECT 
MAX(Activity_Date) AS MaxActivity_Date, Bond_Number
FROM #testtable
GROUP BY Bond_Number)t1
On t1.Bond_Number = t.Bond_Number
AND t1.MaxActivity_Date = t.Activity_Date
WHERE t1.Bond_Number IS NULL

SELECT * FROM #testtable

drop table #testtable

output
------------------------------------
before deletion
---------------------------------------
Bond_Number	Activity_Date
110	2010-05-12 00:00:00.000
110	2011-09-02 00:00:00.000
110	2011-03-02 00:00:00.000
112	2010-12-02 00:00:00.000
115	2007-12-23 00:00:00.000
110	2009-05-12 00:00:00.000
322	2011-11-18 00:00:00.000
112	2010-11-08 00:00:00.000
115	2005-06-02 00:00:00.000
322	2009-01-01 00:00:00.000


after deletion
-------------------------------------
Bond_Number	Activity_Date
110	2011-09-02 00:00:00.000
112	2010-12-02 00:00:00.000
115	2007-12-23 00:00:00.000
322	2011-11-18 00:00:00.000

you can clearly see from above that it retains only max records and deletes others

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources