Deleting duplicates using CTE - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Deleting duplicates using CTE

Author

Topic

SadafKhan85
Starting Member

8 Posts

Posted - 2013-07-03 : 07:59:01

My below query will help in deleting duplicates from any given table using CTE (common table expression).

WITH CTE (Col1
,Col2
,Col3
,Col4
,Col5
,COl6
,DupCount)
AS
(
SELECT Col1
,Col2
,Col3
,Col4
,Col5
,COl6
,ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3, Col4, Col5, COl6
ORDER BY Col1, Col2, Col3, Col4, Col5, COl6 ) AS DupCount
FROM Table_name
)
DELETE
FROM CTE
WHERE DupCount > 1

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-03 : 08:35:40

SadafKhan85, while what you posted certainly would be useful for some readers, this forum (New to SQL Server Programming) is for question/answer type of thing rather than for posting unsolicited tips or tutorials. Within SQLTeam, there is one forum - script library http://www.sqlteam.com/forums/forum.asp?FORUM_ID=11 - which may be more appropriate for such things. Even so, I am higly doubtful whether something simple and well-known as what you have posted adds any value.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-03 : 08:47:04

By the way, you can simplify query like shown below; there is no need for a subquery within cte:

;WITH cte AS
(
	SELECT ROW_NUMBER() OVER 
		( 
			PARTITION BY Col1, Col2, Col3,Col4, Col5, COl6 
			ORDER BY Col1, Col2, Col3, Col4, Col5, COl6 
		) AS DupCount
	FROM     Table_name
) DELETE FROM CTE WHERE   DupCount > 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 08:48:52

Actually you dont even need CTE. you can do it inline


DELETE t 
FROM 
(
SELECT ROW_NUMBER() OVER 
		( 
			PARTITION BY Col1, Col2, Col3,Col4, Col5, COl6 
			ORDER BY Col1, Col2, Col3, Col4, Col5, COl6 
		) AS DupCount
	FROM     Table_name
)t
WHERE   DupCount > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 08:49:25

Actually you dont even need CTE. you can do it inline


DELETE t 
FROM 
(
SELECT ROW_NUMBER() OVER 
		( 
			PARTITION BY Col1, Col2, Col3,Col4, Col5, COl6 
			ORDER BY Col1, Col2, Col3, Col4, Col5, COl6 
		) AS DupCount
	FROM     Table_name
)t
WHERE   DupCount > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-03 : 11:36:13

quote:
Originally posted by visakh16

Actually you dont even need CTE. you can do it inline

DELETE t
FROM
(
SELECT ROW_NUMBER() OVER
(
PARTITION BY Col1, Col2, Col3,Col4, Col5, COl6
ORDER BY Col1, Col2, Col3, Col4, Col5, COl6
) AS DupCount
FROM Table_name
)t
WHERE DupCount > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

My guess is that the two are equivalent in that they would (should) end up using the same query plan.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 13:33:51

quote:
Originally posted by James K

quote:
Originally posted by visakh16

Actually you dont even need CTE. you can do it inline

DELETE t
FROM
(
SELECT ROW_NUMBER() OVER
(
PARTITION BY Col1, Col2, Col3,Col4, Col5, COl6
ORDER BY Col1, Col2, Col3, Col4, Col5, COl6
) AS DupCount
FROM Table_name
)t
WHERE DupCount > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

My guess is that the two are equivalent in that they would (should) end up using the same query plan.

May be
I was just telling it can be done inline and doesnt mandate need of a CTE to be defined

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-04 : 10:38:22

quote:
I was just telling it can be done inline and doesnt mandate need of a CTE to be defined

What you posted earlier and this statement in your latest reply conveys the implied assertion that

a) CTE is a bad thing/heavy weight approach/something to be avoided unless mandated
b) Inline views/derived tables are superior/lightweight/desirable and to be preferred if possible
c) Here is my better way achieving the same results using the inline view method; use this over and above the CTE aproach that was posted earlier.

I was simply disputing that implied assertion.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-04 : 12:08:08

quote:
Originally posted by James K

quote:
I was just telling it can be done inline and doesnt mandate need of a CTE to be defined

What you posted earlier and this statement in your latest reply conveys the implied assertion that

a) CTE is a bad thing/heavy weight approach/something to be avoided unless mandated
b) Inline views/derived tables are superior/lightweight/desirable and to be preferred if possible
c) Here is my better way achieving the same results using the inline view method; use this over and above the CTE aproach that was posted earlier.

I was simply disputing that implied assertion.

Where have I suggested/implied all of the above?
My only point here was that above case is a scenario which does not necessarily need a CTE usage. I've not suggested anything in favour of or opposing CTEs here in terms of performance. If that was my intention, I would have supplimented it with some test results at least.

I dont know how you manage to imply all these out of my explanation where I never ever spoke in terms of performance

My only request is not to associate unsuggested/unintended inferences from my explanation and dispute over it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources