DELETE SAME ROWS - 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 SAME ROWS

Author

Topic

yaman
Posting Yak Master

213 Posts

Posted - 2010-12-09 : 11:14:13

Sir ,

I have one table test

colums are - T1

T1
==
1
1
1
3
3
3
3
==

I want to delete same record except top 1 of same records after delete my result like this

T1
==
1
3
==

Please help me out sir .

Yaman

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-09 : 11:27:50

set rowcount 1
while exists (select t1 from tbl group by t1 having count(*) > 1)
delete tbl
from tbl t
join (select t1 from tbl group by t1 having count(*) > 1) a
on t.t1 = a.t1
set rowcount 0

may be beter to get distinct valuse into a temp table and recteate

select distinct t1 into #a from tbl
begin tran
delete tbl
insert tbl select t1 from #a
commit tran

==========================================
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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-09 : 11:52:30

if sql 2005 or above


delete t
from
(
select row_number() over (partition by T1 order by T1) AS rn
from table
)t
where rn >1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources