How to delete duplicate rows from a table?

 Posted by Rajnilari2015 on 10/23/2016 | Category: Sql Server Interview questions | Views: 313 | Points: 40
Answer:

Suppose we have a table tblPerson with the below records

PersonName PersonAge 

----------------- ------------------
Person1 30
Person1 30
Person2 50
Person3 40
Person2 50

We need the output as

PersonName PersonAge 

----------------- ------------------
Person1 30
Person2 50
Person3 40


We can achieve it as

;With Cte As(
Select Rn=Row_Number() Over(Partition By PersonName Order by PersonName) ,p.*
From tblPerson)
Delete from Cte
Where Rn >1

The Row_Number() function will make a ranking as

Rn PersonName            PersonAge

---- ------------------ -----------------
1 Person1 30
2 Person1 30
1 Person2 50
1 Person3 40
2 Person2 50


The Delete statement will delete those rows where the Rn > 1.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Bhupentiwari on: 10/24/2016 | Points: 10
There is mistake in the above query. partition by is mission

Login to post response