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