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
Jignesh TrivediPosted Jul 30, 2012, 12:04 AM
CTE is really good idea to delete duplicate record.
thx.
Satyapriya NayakPosted Jul 29, 2012, 2:19 PM