Every once in a while I encounter a data migration scenario where the destination table doesn’t enforce a Unique Index and when re-running data migration code if you forget to purge records in the destination prior to inserting without the unique index you could end up with duplicates.
Today’s posting focuses on one way of deleting the duplicates – using the Row_Number() OVER (PARTITION BY ) ORDER BY syntax in SQL Server to assign row numbers by what I want to be the Unique records and then delete those that are duplicates.
In the example below, I have a table that stores salesperson assignments – it populates a table called the CommissionSalespersonDefaults.
The inserts in this example were run 3 times over a period of months. But since the delete was omitted, every time the statements were run I ended up with duplicates/triplicates, etc. in that table, like this:
|
CommissionSalespersonDefaultsID |
CustomerCode |
ShipToCode |
Commissionable |
Salesperson1 |
|
22 |
20THC |
TRUE |
AA1 |
|
|
9538 |
20THC |
TRUE |
PrI |
|
|
21971 |
20THC |
TRUE |
PrI |
There should only be one record for this CustomerCode/ShipToCode combination in this table – I need to delete two of them, but in case some attribute is different like in this case the sales rep changed from the 1st migration to the 2nd/3rd I only want to keep the last one – the one with ID 21971.
There are a number of ways to get this done, but for today’s article I’m focusing on the Row_Number() OVER (PARTITION BY ) ORDER BY feature in SQL Server.
Here’s a Select statement showing just the first 9 records with this in action:
I like to see what I’m about to do when it comes to delete statements so I usually start with a select statement giving me a preview of what’s about to happen:
SELECT TOP 9 * FROM CommissionSalespersonDefaults s
INNER JOIN (
SELECT *
, ROW_NUMBER()
OVER (
PARTITION BY CustomerCode, ISNULL(ShipToCode, ”)
ORDER BY CommissionSalespersonDefaultsID DESC
) AS RowNumber
FROM CommissionSalespersonDefaults
) Numbered ON s.CommissionSalespersonDefaultsID = Numbered.CommissionSalespersonDefaultsID
What the query does is add the RowNumber, with it resetting after each change in whatever you put in the Partition By part – in this case, in each change in CustomerCode+ShipToCode. The ORDER BY Clause tells SQL Server the order in which to order the records prior to assigning the row numbers. Here’s a sample of the output
|
RowNumber |
CommissionSalespersonDefaultsID |
CustomerCode |
ShipToCode |
Commissionable |
Salesperson1 |
|
1 |
21971 |
20THC |
TRUE |
PrI |
|
|
2 |
9538 |
20THC |
TRUE |
PrI |
|
|
3 |
22 |
20THC |
TRUE |
AA1 |
|
|
1 |
21972 |
212 |
TRUE |
SCS |
|
|
2 |
9539 |
212 |
TRUE |
SCS |
|
|
3 |
23 |
212 |
TRUE |
SCS |
|
|
1 |
21973 |
280T |
TRUE |
TSW |
|
|
2 |
9540 |
280T |
TRUE |
TSW |
|
|
3 |
24 |
280T |
TRUE |
TSW |
So it’s easy to see here that what we want to delete are any records where the RowNumber is > 1.
DELETE CommissionSalespersonDefaults
FROM CommissionSalespersonDefaults
INNER JOIN (
SELECT *
, ROW_NUMBER()
OVER (
PARTITION BY CustomerCode, ISNULL(ShipToCode, ”)
ORDER BY CommissionSalespersonDefaultsID DESC
) AS RowNumber
FROM CommissionSalespersonDefaults
) Numbered ON CommissionSalespersonDefaults.CommissionSalespersonDefaultsID = Numbered.CommissionSalespersonDefaultsID
WHERE RowNumber > 1
Now I have a table containing only the most recently imported records.
Darren Myher's Blog 