Need to remove duplicates from entire table by

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need to remove duplicates from entire table by

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-05-25 : 09:21:18

Need to remove duplicates from table by keeping one. The table contains 5 column. No primary key for the table. The Columns are as follows.
Need to find the duplicates from all the columns not based on one column.

CName CDesg Cdept CGender CResult

when i use the query Slect distinct * from table, i am getting all distinct results. But i need to remove permannently the duplicate rows by keeping one.

Ex:

CName CDesg Cdept CGender CResult
test te kpt M yes
test te kpt M yes
pos jp obm F yes
Uni lk jhp F yes

Need output as
--------------
CName CDesg Cdept CGender CResult
test te kpt M yes
pos jp obm F yes
Uni lk jhp F yes

G. Satish

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-25 : 09:32:57

If you had a primary key (even a surrogate one) this would be very easy. Why no primary key? Your table is really a HEAP with all the performance disadvantages that entails

Cam you add a surrogate primary key?

This will *work* but it's not pretty


DECLARE @foo TABLE (
	CName NVARCHAR(255)
	, CDesg NVARCHAR(255) 
	, Cdept NVARCHAR(255)
	, CGender NVARCHAR(255)
	, CResult NVARCHAR(255)
	)

DECLARE @bar TABLE (
	CName NVARCHAR(255)
	, CDesg NVARCHAR(255) 
	, Cdept NVARCHAR(255)
	, CGender NVARCHAR(255)
	, CResult NVARCHAR(255)
	)

-- Data Set
INSERT @foo
          SELECT 'test', 'te', 'kpt', 'M', 'yes'
UNION ALL SELECT 'test', 'te', 'kpt', 'M', 'yes'
UNION ALL SELECT 'pos', 'jp', 'obm', 'F', 'yes'
UNION ALL SELECT 'Uni', 'lk', 'jhp', 'F', 'yes'

SELECT * FROM @foo

--Populate @bar
INSERT @bar SELECT DISTINCT * FROM @foo

--wipe @foo
DELETE FROM @foo

--Populate @foo
INSERT @foo SELECT * FROM @bar

SELECT * FROM @foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-25 : 09:33:24

select * from (select row_number() over(partiiton by CName, CDesg, Cdept, CGender, CResult order by cname) as rid, * from tablename) s where rid = 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-25 : 09:33:35

[code]
delete d
from
(
select CName CDesg Cdept CGender CResult,
row_no = row_number() (partition by CName CDesg Cdept CGender CResult
order by CName CDesg Cdept CGender CResult)
from table
) d
where d.row_no > 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-25 : 10:34:19

quote:

Originally posted by khtan


delete d
from   
(
    select CName CDesg Cdept CGender CResult, 
           row_no = row_number() over (partition by CName, CDesg, Cdept, CGender, CResult
                                      order by CName, CDesg, Cdept, CGender, CResult)
    from   table
) d
where row_no > 1

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-25 : 10:38:49

oh dear . . this is a bad . . . i thought i saw those comma there

thanks

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources