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

Thanks to the team at 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)
(SELECT   Product
          , SaleDate
          , SalePrice
          , Ranking = DENSE_RANK() 
          OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC) 
FROM SalesHistory
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 there is a combination that will help your current situation. Please note, this is for SQL Server 2005 and above.


Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter