I had previously penned down two popular snippets regarding deleting duplicate rows and counting duplicate rows. Today, we will examine another very quick code snippet where we will delete duplicate rows using CTE and ROW_NUMBER() feature of SQL Server 2005 and SQL Server 2008.
This method is improved over the earlier method as it not only uses CTE and ROW_NUMBER, but also demonstrates the power of CTE with DELETE statement. We will have a comprehensive discussion about it later in this article. For now, let us first create a sample table from which we will delete records.
/* Create Table with 7 entries - 3 are duplicate entries */
CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
GO
The above table has total 7 records, out of which 3 are duplicate records. Once the duplicates are removed we will have only 4 records left.
/* It should give you 7 rows */
SELECT *
FROM DuplicateRcordTable
GO
The most interesting part of this is yet to come. We will use CTE that will re-generate the same table with additional column, which is row number. In our case, we have Col1 and Col2 and both the columns qualify as duplicate rows. It may be a different set of rows for each different query like this. Another point to note here is that once CTE is created DELETE statement can be run on it. We will put a condition here – when we receive more than one rows of record, we will remove the row which is not the first one. When DELETE command is executed over CTE it in fact deletes from the base table used in CTE.
/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO
It is apparent that after delete command has been run, we will have only 4 records, which is almost the same result which we would have got with DISTINCT, with this resultset. If we had more than 2 columns and we had to run unique on only two columns, our distinct might have not worked here . In this case, we would have to use above the mentioned method.
/* It should give you Distinct 4 records */
SELECT *
FROM DuplicateRcordTable
GO
This method is a breeze and we can use this for SQL Server version 2005 and the later versions.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
This new feature is very good.
I use this key word very often.
The other good one is Simple OVER PARTITION BY with out ROW_NUMBER () and RANK.
We extensively use GROUP BY and we all know problem with GROUP BY (We should have all selected column from select clause in group by clause, other wise we cannot use group by). But by using OVER PARTITION BY we can group by few of many selected column in select clause.
I believe this feature was first introduced in ORACLE. And then in SQL Server.
But this is really a cool feature to use.
Thanks.
Imran.
Don’t forget the KB Article on the same topic:
http://support.microsoft.com/kb/139444
Pretty nice post. I just stumbled upon your site and wanted to say
that I have really liked browsing your blog posts. In any case
I’ll be subscribing to your blog and I hope you write again soon!
Glad to note that your site is back up.
Shocked at the way customer support reacted…
Take Care
This is a neat mehod, Initially i thought deleting from CTE wouldn’t delete the dup records from the base table, surely it does, thanks a bunch.
Nice method to avoid using temp/derived and group by operation.
Can we perform update operation on cte? will it reflect the base table also?.
Thanks in advance.
I have two smalldatetime fields say validfrom and validtill. how do I query to get results between the both dates??
superb t-sql statement to delete duplicate rows > 1.
This is really a great and simple approach. Thanks for this post.
Very nice…………
Thanks!
Saved me a LOT of time and struggles!
Without a lot introducing a lot of unnecessary complexities, you provide high quality solutions. Thanks, again!
this ERROR OCCURS after executing the above sql
Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
name regno
kkk 1111
kkk 1111
kkk 1111
ppp 1110
ppp 1110
how to delete these duplicatess
@karthikeyan
What are the datatypes of those COLUMNs?
i have Q
how can delete row from tow tables have relationship
between tables
Thanks! Good Article :)
thnx.. really nice article… good use of ROWNUMBER()…..
Hey Pinal,
loved ur blog..
am a newbie to sql server 2005…nd would really appreciate your help..
i have more columns than 2 that you have(around 6) in which i have the data…am a little confused as to how to write the RowNumber() and Over() methods..
thnx in advance
Yes, I have understood the answer for the above question.
But, I have been trying to import data from an excel table to sql server 2005 with avoiding the duplicate rows. So, how should I be able to implement the above code to a C# program.
Thank You very much
Is there any way that I can be helped upon…thank u very much
Hey Pinal very very very nice postings… really helpful
I dont know how to remove rows from excel using sql server query… please post about this…
Thanx in advance :)
Nice article! Thanks!
Hey Pinal,
Superb Blog yar !!!! I really loved u r blog. Its wonderful and huge collections…
I am new one.. now onwards i will share my knowledge through u r blog….
Keep it up…
This blog spot cleared majority of my SQL clarification. CTE is used in the above query to delete duplicate rows in such a cute way.
Great Job….
NIce Blog..Subscribing it..
greate job!
i was trying each and every query from 20-30 web pages for doing the same thing but none of them worked.
this is really awesome.
thanks. :)