[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 741
  • Last Modified:

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
0
Greens8301
Asked:
Greens8301
1 Solution
 
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now