Thanks to the guys at TechRepublic.com I have used CTEs to delete duplicates with ease in SQL Server 2005 and higher. Here is the link. Here meat of the article a sample:
;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking)
AS
(
SELECT
Product, SaleDate, SalePrice,
Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC)
FROM SalesHistory
)
DELETE FROM SalesCTE
WHERE Ranking > 1
Very easy and handy!
