Common Table Expressions (CTE) in SQL Server 2005 and Up

Thanks to the team at TechRepublic.com and BOL, I have used CTEs to delete duplicates with ease in SQL Server 2005 and higher. Learn more about CTEs ¬†at TechRepublic. 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! Check out BOL for all kinds of information regarding Dense_Rank(), NEW_ID(), and PARTITION BY. While you’re there, check out RANK(), NTILE(), and ROW_NUMBER() I’m sure … Continue reading Common Table Expressions (CTE) in SQL Server 2005 and Up