Article Link.

A less elegant way to clean up duplicate rows would be to use the T-SQL SET ROWCOUNT command. If you have 3 duplicate rows and want to delete 2 of them, SET ROWCOUNT 2 combined with a proper WHERE clause will clean up the 2 additional rows. Then a SET ROWCOUNT 0 (Zero) will turn the option off.

As always, it is a great idea to backup you data before you start deleting rows.

-- Donald

Delete top 2 from table where [Condition]

This is a question I usually ask interviewees.
There are several ways to do this - the best way depends on the size, number of dups, how much the database is acessed...

The solution given is only really the best for a small off-line table imho, but I would expect every candidate to at least come up with this as a starting point.

This shouldn't allow anonymous comments anymore. Of course I already tried this once . . .

I'll probably get round to posting some other methods here later when I have time.
Some are a bit complicated and need testing because I usually get them wrong first time.

These are some ways of deleting dups.
Some are better than others depending on:
size of table.
whether system can be brought down to carry out the action
Space available
Number of duplicates
How much time is available - both to carry out the action and to write the script to do it.

I create any temp table by a select into but this should be replaced by a create statement.
I have also tried to place transactions in the correct place but have omitted error processing.
To have less impact on a live system a pause could be put in the loops

(This comes without any guarantee but should be close)

drop table #a
go
create table #a (i int, j int, k int)

insert #a select 1,1,1
insert #a select 1,1,1
insert #a select 1,1,1
insert #a select 1,1,1
insert #a select 2,1,1
insert #a select 2,1,1
insert #a select 2,2,1
insert #a select 2,2,2
insert #a select 2,2,3
insert #a select 2,2,4
insert #a select 3,3,3
insert #a select 3,3,3
insert #a select 3,3,3

1. simple table recreate
select * into #b from #a where 1 = 0
insert #b select distinct * from #a
begin tran
delete #a
insert #a select * from #b
commit tran
drop table #b

2. delete and replace duplicates
select * into #b from #a where 1 = 0
insert #b select i,j,k from #a group by i,j,k having count(*) > 1
begin tran
delete #a from #b where #a.i = #b.i and #a.j = #b.j and #a.k = #b.k
insert #a select * from #b
commit tran
drop table #b

3. delete duplicates one by one leaving single row
set rowcount 1
select 1
while @@rowcount > 0
delete #a
where 1 < (select count(*) from #a a2 where #a.i = a2.i and #a.j = a2.j and #a.k = a2.k)
set rowcount 0

4. delete all duplicates for one row value at a time
select *, cnt = 0 into #b from #a where 1 = 0
declare @rowcount int
select 1
while @@rowcount <> 0
begin
insert #b (i,j,k,cnt) select top 1 i,j,k, count(*) - 1 from #a group by i,j,k having count(*) > 1
select @rowcount = cnt from #b
set rowcount @rowcount
delete

4. delete all duplicates for one row value at a time
select *, cnt = 0 into #b from #a where 1 = 0
declare @rowcount int
select 1
while @@rowcount <> 0
begin
insert #b (i,j,k,cnt) select top 1 i,j,k, count(*) - 1 from #a group by i,j,k having count(*) > 1
select @rowcount = cnt from #b
set rowcount @rowcount
delete #a
from #b
where #a.i = #b.i and #a.j = #b.j and #a.k = #b.k
set rowcount 0
delete #b
end
drop table #b

5. similar to above but all rows to delete gathered at beginning
select *, cnt = 0, identity(int,1,1) as id into #b from #a where 1 = 0
insert #b (i,j,k,cnt) select i,j,k, count(*) - 1 from #a group by i,j,k having count(*) > 1
declare @id int, @rowcount int
while exists (select * from #b)
begin
select @id = min(id) from #b
select @rowcount = cnt from #b
set rowcount @rowcount
delete #a
from #b
where #a.i = #b.i and #a.j = #b.j and #a.k = #b.k
and #b.id = @id
set rowcount 0
delete #b where id = @id
end
drop table #b