Rudy Rodarte . com

Information

This article was written on 22 Jan 2010, and is filled under SQL Blogs.

Current post is tagged

, , ,

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

Thanks to the guys 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 there is a combination that will help your current situation. Please note, this is for SQL Server 2005 and above.

Rudy

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

Leave a Reply