How to remove adjacent duplicate records

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)

How to remove adjacent duplicate records

Author

Topic

praveen_vejandla
Starting Member

1 Post

Posted - 2012-10-25 : 03:54:36

Hi,

In SQL 2008, is there any way to remove only adjacent/contiguous duplicate records? Only if a record repeats immediately, then it should be deleted.

ex:

name value

t1 10
t2 11
t2 11-------> to be deleted
t2 11-------> to be deleted
t2 15
t1 10
t1 10-------> to be deleted
t1 10-------> to be deleted
t4 10
t5 10
t5 20
t5 20------> to be deleted

Result should be as below.

name value

t1 10
t2 11
t2 15
t1 10
t4 10
t5 10
t5 20

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-10-25 : 04:04:02

delete dt from
(select *, row_number() over(partition by name,value order by name,value)as rn from YourTable)dt
where rn > 1

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources