Delete duplciate records - 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 2008 Forums

Transact-SQL (2008)

Delete duplciate records

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2014-03-22 : 06:13:01

Hi
If I wanted to delete all records where there is a duplicate email but keep the one row with the most recent date, so in the example data below the only two records remaining would be, ID 3 for the test@test.com email and ID 8 for the xxx@xxxx.com email.

What would be the best way to do this?
Thanks for looking

Id email date
1 test@test.com 2014-10-02
2 test@test.com 2014-10-01
3 test@test.com 2014-10-03
4 xxx@xxxx.com 2014-10-05
5 xxx@xxxx.com 2014-10-02
6 xxx@xxxx.com 2014-10-01
7 xxx@xxxx.com 2014-10-03
8 xxx@xxxx.com 2014-10-08

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-22 : 08:27:10

[code]

create table #tblEmails
(
ID int identity(1,1),
email varchar(500),
date date )

Insert into #tblEmails(email,[date])
select 'test@test.com' email, '2014-10-02' [date] union all
select 'test@test.com','2014-10-01' union all
select 'test@test.com' ,'2014-10-03' union all
select 'xxx@xxxx.com' ,'2014-10-05' union all
select 'xxx@xxxx.com' ,'2014-10-02' union all
select 'xxx@xxxx.com' ,'2014-10-01' union all
select 'xxx@xxxx.com' ,'2014-10-03' union all
select 'xxx@xxxx.com' ,'2014-10-08'

delete
from #tblEmails
where ID in (

select ID
from
(
select
ID
,Email
,Date
,ROW_NUMBER() OVER(Partition by email Order by [Date] desc) as rn
from
#tblEmails )A
where rn <> 1 )

select * from #tblEmails

drop table #tblEmails
[/code]

sabinWeb MCP

mshsilver
Posting Yak Master

112 Posts

Posted - 2014-03-22 : 09:58:12

Many thanks for this it works perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources