Saturday, 13 April 2013

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