Remove duplicate rows - Impossible to find a decisive answer

Question

You'd immediately think I went straight to here to ask my question but I googled an awful lot to not find a decisive answer.

Facts: I have a table with 3.3 million rows, 20 columns. The first row is the primary key thus unique.

I have to remove all the rows where column 2 till column 11 is duplicate. In fact a basic question but so much different approaches whereas everyone seeks the same solution in the end, removing the duplicates.

I was personally thinking about GROUP BY HAVING COUNT(*) > 1 Is that the way to go or what do you suggest?

Thanks a lot in advance! L

Answer 1

As a generic answer:

WITH cte AS (
  SELECT ROW_NUMBER() OVER (
      PARTITION BY <groupbyfield> ORDER BY <tiebreaker>) as rn
  FROM Table)
DELETE FROM cte
WHERE rn > 1;

I find this more powerful and flexible than the GROUP BY ... HAVING. In fact, GROUP BY ... HAVING only gives you the duplicates, you're still left with the 'trivial' task of choosing a 'keeper' amongst the duplicates.

ROW_NUMBER OVER (...) gives more control over how to distinguish among duplicates (the tiebreaker) and allows for behavior like 'keep first 3 of the duplicates', not only 'keep just 1', which is a behavior really hard to do with GROUP BY ... HAVING.

The other part of your question is how to approach this for 3.3M rows. Well, 3.3M is not really that big, but I would still recommend doing this in batches. Delete TOP 10000 at a time, otherwise you'll push a huge transaction into the log and might overwhelm your log drives.

And final question is whether this will perform acceptably. It depends on your schema. IF the ROW_NUMBER() has to scan the entire table and spool to count, and you have to repeat this in batches for N times, then it won't perform. An appropriate index will help. But I can't say anything more, not knowing the exact schema involved (structure of clustered index/heap, all non-clustered indexes etc).

Answer 2

Group by the fields you want to be unique, and get an aggregate value (like min) for your pk field. Then insert those results into a new table.

Answer 3

If you have SQL Server 2005 or newer, then the easiest way would be to use a CTE (Common Table Expression).

You need to know what criteria you want to "partition" your data by - e.g. create partitions of data that is considered identical/duplicate - and then you need to order those partitions by something - e.g. a sequence ID, a date/time or something.

You didn't provide much details about your tables - so let me just give you a sample:

;WITH Duplicates AS
(
   SELECT  
       OrderID,
       ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RowN
   FROM
       dbo.Orders
)
DELETE FROM dbo.Orders
WHERE RowN > 1

The CTE ( WITH ... AS :... ) gives you an "inline view" for the next SQL statement - it's not persisted or anything - it just lives for that next statement and then it's gone.

Basically, I'm "grouping" (partitioning) my Orders by CustomerID, and ordering by OrderDate. So for each CustomerID, I get a new "group" of data, which gets a row number starting with 1. The ORDER BY OrderDate DESC gives the newest order for each customer the RowN = 1 value - this is the one order I keep.

All other orders for each customer are deleted based on the CTE (the WITH..... expression).

You'll need to adapt this for your own situation, obviously - but the CTE with the PARTITION BY and ROW_NUMBER() are a very reliable and easy technique to get rid of duplicates.

Answer 4

If you don't want to deal with a new table delete then just use DELETE TOP(1). Use a subquery to get all the ids of rows that are duplicates and then use the delete top to delete where there is multiple rows. You might have to run more than once if there are more than one duplicate but you get the point.

DELETE TOP(1) FROM Table
WHERE ID IN (SELECT ID FROM Table GROUP BY Field HAVING COUNT(*) > 1)

You get the idea hopefully. This is just some pseudo code to help demonstrate.