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

Thanks
Greens8301Asked:
Who is Participating?
 
reb73Commented:
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)
go
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

0
 
brad2575Commented:
You can use a Row Number or a RANK function:
http://msdn.microsoft.com/en-us/library/ms186734(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms186734(SQL.90).aspx

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

Similar for Row
0
 
meispiscesCommented:
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 :   http://support.microsoft.com/kb/139444
 
0
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.