how to delete duplicate rows in table sql server 2005

I have 6 identical rows of 4 columns table in sql server 2005
I like to have a script to delete the duplicate rows
Is there a way to list rowid ( oracle concept) along with the columns to delete one of the duplicate rows

Who is Participating?
See template code below using a temporary table for testing, you should be able to adapt the delete statement below to suit your needs -
create table #tmp (a int, b int,c int, d int)
insert #tmp values (0,0,0,0)
insert #tmp values (1,1,1,1)
insert #tmp values (2,2,2,2)
insert #tmp values (3,3,3,3)
insert #tmp values (4,4,4,4)
insert #tmp values (5,5,5,5)
go 2
select * from #tmp
delete tmp 
from	(select *, row_number() over (partition by a,b,c,d order by a,b,c,d) as rn
	from #tmp) tmp
where rn > 1
drop table #tmp

Open in new window

You can use a Row Number or a RANK function:

if you RANK them just in the "partition by" field list ALL the fields then delete where Rank is <> '1'

Similar for Row
Delete  from MyTable where ID Not IN
     select  max(ID) from MyTable Group By Duplicate Colum1, Duplicate Column2,Duplicate Column 3
Table must have identity column which would identify the duplicate records. Here in MyTable ID as identity column  and duplicate columns are duplicate columns 1,2 & 3 (very obvious to know...!!!)
Also refer :
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.