How to delete duplicate rows from a table?

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.