Delete older repeating lines using ROW_NUMBER() + Partition By + CTE’s

The Row_Number() function with Partition By and CTE’s it’s a very powerful feature avaliable to Sql Server developers, I’ll make a demonstration of a delete operation that I needed to apply in a recent project that I’am involved.

Imagine that you have an Products table like:

image

As you can see there’s two repeating products: “Bike” and “Pen”.

Now if you want to delete the repeating lines of the table above but only the older ones as far as I can tell it’s not possible to do with only one TSQL statement (for example you need a cursor), but if you use Row_Number() plus Common Table Expressions (CTE’s) that is indeed possible.

With Row_Number() we can obtain the number of the row in the result but we can also group by (Partition By) a field in this case “ProductId” and order the groupings by DateModified:

SELECT *, ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY DateModified DESC) as Row
FROM Products p

image

Now with a CTE we can perform a Delete operation over that result like this:

WITH ProductsOrdered AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY DateModified DESC) as Row
    FROM dbo.Products p
)
DELETE FROM ProductsOrdered
WHERE Row > 1

And that’s it, with only this TSQL statement tou can delete the repeating lines of Products table but only the older ones.

For more information check this excelent article: http://www.sqlmag.com/Article/ArticleID/49240/sql_server_49240.html

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s