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.
Watch the view to see the above concept in action:
Reference : Pinal Dave (https://blog.sqlauthority.com)
146 Comments. Leave new
THANK YOU VERY MUCH!!! Your sharing saves me so much time and helps me resolve the issue quickly.
delete a from tr_myactivity a where (select count(*) from tr_myactivity b where a.activitydate = b.activitydate)>1
Did you test your query with row_number() approach?
I think your method may take more time on the large set of data
Really a Good article for beginner.
try this:
create table dupdata(id int,name varchar(30),sal money)
insert into dupdata values(1,’veeru’,2100)
insert into dupdata values(1,’veeru’,2100)
insert into dupdata values(2,’sud’,2100)
insert into dupdata values(2,’sud’,2100)
alter table dupdata add rowid int identity(1,1)
delete from dupdata where rowid in(select max(rowid) from dupdata group by id,name,sal
having count(*)>1)
alter table dupdata drop column rowid
select * from dupdata
This CTE trick is the best thing since sliced bread!
Unfortunately, I had invested (Wasted) several hours trying to delete duplicate rows by using inner join and by first inserting all the duplicates into a Temp table. Whew!
This may have saved my sanity :)
THANK YOU Dave!
Exactly what I needed. THANK YOU VERY MUCH!
Can any body guide how to use CTE if table has several columns. I am using following query but getting error.
WITH CTE ([MSC-S KPI],*,DuplicateCount)
AS
(
SELECT [MSC-S KPI],*
ROW_NUMBER() OVER(PARTITION BY [MSC-S KPI],* ORDER BY [MSC-S KPI]) AS DuplicateCount
FROM dbo.KPI_pl$
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
Go
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘*’.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ‘ROW_NUMBER’.
Is it necessary to enlist all the columns in CTE expression.?
How can I add mins on current time and display
time incrementing by added mins
For eg:
13:25 + 15 mins = 13:14
or
13:45 + 15 mins = 14:00
or
13:50 + 15 mins = 14:05
Thank you in advance
Sam
select GETDATE(),DATEADD(Minute,15,getdate())
I actually figured out the answer:
Select Convert(char(5),DateAdd(mi,15,GetDate()),114)
hello sir
your duplicate rows query is very best
sir i have 6 column in table (employee)
column name
ip_code
11–12345656
11-23657896
11-34784523
11-09864584
11-54830765
11-67094587
means total column 200 only
i want a that only 5 interger value come in output
means
11-123
11-236
11-347
11-098
11-548
plz sir answer this question becoz this query is very important for me
thankyou
pinal dave (sir)
select top 10 LEFT(,20) from
select LEFT(,20) from
thnx ..nice post..
Very short and impressive solution. Thanks a lot.
nice post …
this is an interview question ..
thanks..
Very Useful article.
Thanks
Shyam
this is very good article.
I have a result set from aTable Colors like follows which having coloms Name and Color
Name color
A Green
A RED
A Yellow
Davis Bloon
Davis Red
I want to replace that the result set as follows.
Name color
A Green
RED
Yellow
Davis Bloon
Red
This is the presentation issue. Where do you want to show such formatted data?
WITH cte (clr,ROwNum)as
(SELECT clr,row_Number() OVER(PARTITION BY LEFT(clr,CHARINDEX(SPACE(1),clr)-1) ORDER BY LEFT(clr,CHARINDEX(SPACE(1),clr)-1)) FROM color)
–select * from cte where ROwNum>1
–select clr,CHARINDEX(SPACE(1),clr),
–LEFT(clr,CHARINDEX(SPACE(1),clr)-1),
UPDATE cte SET clr=Right(clr,len(clr)-CHARINDEX(SPACE(1),clr))–AS ToUpdate from cte
WHERE ROwNum>1
This post is very very helpful.
Hi Panel but i still confuse about ur query
i try at home and get the result
but i don’ t understand why
we write
DELETE FROM CTS WHERE DC>1
it means the delete the records where dc is greater than 1
but in ur row number column no records is greater than 1
then how ur query work
Please help me out
Short Knowledge is Bigger hurdle in Success.
Great post!!
Thanks
what is CTE? Can you please elaborate
comman Table Expression
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190766(v=sql.105)