Delete duplicate - 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

General SQL Server Forums

New to SQL Server Programming

Delete duplicate

Author

Topic

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-21 : 12:27:19

Hi have this situation
create table dbo.test (name varchar(50) null,ip varchar(15) null,status varchar(12) null)
insert test values ('1','1.1.1.1','Available')
insert test values ('2','1.1.1.2','Available')
insert test values ('3','1.1.1.3','Available')
insert test values ('4','1.1.1.4','Unavailable')
insert test values ('1','1.1.1.10','Unavailable')
insert test values ('2','1.1.1.20','Unavailable')
insert test values ('3','1.1.1.30','Unavailable')
insert test values ('4','1.1.1.40','Unavailable')

I need to delete duplicate but with respect to status.
example of desired OUTPUT:
1
2
3
4
4
This means if the status of the same name is similar dont delete it, otherwise delete if status is different.

I have came out with this query, it WORKS perfect, but I'm look if someone has an additional suggestion that's better.

Appreciated,

kml

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-21 : 12:59:23

;with cte as
(
select *, seq = rank() over (partition by name order by status)
from test
)
select name
from cte
where seq <> 1

if you actually want to delete from the table
;with cte as
(
select *, seq = rank() over (partition by name order by status)
from test
)
delete cte
where seq <> 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-06-22 : 06:56:12

Also Nigel had posted at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256 to show various techniques. Nigel you should have had it in your blog

Madhivanan

Failing to plan is Planning to fail

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-22 : 11:34:42

quote:
Originally posted by madhivanan

Also Nigel had posted at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256 to show various techniques. Nigel you should have had it in your blog

Madhivanan

Failing to plan is Planning to fail

Solved,

What you guys think about this query? :

delete from table where name not in (
select name
from table
where status like 'UnAvailable' and name not in (
select name
from table where status like 'Available'))
and status like 'UnAvailable'

kml

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources