SQL SERVER – Delete Duplicate Rows

SQL SERVER – Delete Duplicate Rows

for an example if you are having following table named as products.

title                 

--------------------  

duplicate row        

duplicate row         

not duplicate row    

second duplicate row  

second duplicate row  

we  have used CTE here which adds extra row and aliased that new column as idm. so to understand how it works, we can use following query to describe.

with CTE as(
 select row_number() over(partition by title order by title) as idm,title  from products
)SELECT title ,idm from CTE  

will return following output

title                idm

-------------------- --------------------

duplicate row        1

duplicate row        2

not duplicate row    1

second duplicate row 1

second duplicate row 2

And we will use delete clause instead of select in above query... that's it.. so happy coding... :)

and if you want to delete all duplicate rows. following is query to delete products which is getting repeated. so following query will delete all duplicate and will keep original in table.

with CTE as(
 select row_number() over(partition by title order by title) as idm,title  from products
)DELETE from CTE  where idm>1


and after above query select statement will return following records,

title                 

--------------------  

duplicate row      

not duplicate row    

second duplicate row