Delete Duplicate Rows From Table In Sql Server with No Identity Column in table

Question

If you want to delete Duplicate rows from sql server table with no identity column here is the trick

Consider table given below say sampletable

id  data

1   A

2   B

1   A

3   C

1   A

2   B

1   A

If you want to delete duplicate rows in sql server

here is the trick with common table expression as given

WITH CTE AS

(

SELECT *,RN=ROW_NUMBER() OVER (PARTITION BY id ORDER BY id DESC) FROM sampletable

)

DELETE FROM CTE WHERE RN > 1