SQL Delete Duplicates

Before deleting duplicates you have to find them.
In this example, duplicates are identified using a CTE on the Order table.

WITH Cte(FirstName, LastName, 
         OrderDate, RowNumber) AS (
  SELECT FirstName, LastName, OrderDate,
         ROW_NUMBER() OVER (
           PARTITION BY OrderDate, FirstName, LastName
           ORDER BY OrderDate, FirstName, LastName) AS RowNumber
   FROM [Order] O
   JOIN Customer C ON C.Id = O.CustomerId
) 

SELECT * 
  FROM Cte 
  WHERE RowNumber > 1

Note: These are not true duplicates, but it gets the point across. This query returns customers that placed more than one order on the same day.

The ROW_NUMBER() function sequentially numbers rows that are grouped by FirstName, LastName, and OrderDate. Those with a RowNumber > 1 are duplicates of another row.

Next, the duplicate rows need deletion.
Changing SELECT to DELETE in the above query will accomplish this.

WITH Cte(Id, CustomerId, OrderDate, RowNumber) AS (
  SELECT Id, CustomerId, OrderDate,
         ROW_NUMBER() OVER (
           PARTITION BY CustomerId, OrderDate
           ORDER BY OrderDate, CustomerId) AS RowNumber
   FROM [Order] O
) 

DELETE
  FROM Cte
 WHERE RowNumber > 1

Note: This query always deletes the second row it finds which may, or may not, be desirable. A manual inspection step of the duplicates is often required.

SELECT A.Id AS OrderId, FirstName, LastName, 
       A.OrderNumber, A.OrderDate
  FROM [Order] A
  JOIN (

        SELECT C.Id, FirstName, LastName, OrderDate,
               COUNT(O.Id) AS Occurences
          FROM [Order] O
          JOIN Customer C ON C.Id = O.CustomerId
         GROUP BY C.Id, OrderDate, FirstName, LastName
        HAVING COUNT(O.Id) > 1) AS B

    ON A.CustomerId = B.Id AND A.OrderDate = B.OrderDate

Here we have a list with paired duplicates, one after the other.

• Our Sql UNIQUE Constraint
• Our Sql ROW_NUMBER FUNCTION
• Our Sql Find Duplicates Reference