Darren Myher's Blog

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 1^st migration to the 2^nd/3^rd 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.