SQL Find Duplicates

Find the duplicates in the Order table.

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

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

As you can see, with COUNT, GROUP BY and HAVING we can confirm that duplicates exist.

Next, we want the actual rows that are duplicates. This is accomplished by joining the above query with a second one, like so:

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. This allows us to make decisions about which rows to keep and which ones to delete.

One way to prevent duplicates from entering a database is to use constraints. Relevant constraints are Identity, UNIQUE columns, and UNIQUE Indexes. Click the respective links for details.

Note: SQL Server creates a UNIQUE index on a column that is declared UNIQUE. So, the end-result of UNIQUE columns and UNIQUE Indexes are the same.

Duplicates can also be found by using the ROW_NUMBER Window function.

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: This returns the same results as the first query on this page.

• Our Sql GROUP BY Tutorial
• Our Sql UNIQUE Constraint
• Our Sql ROW_NUMBER FUNCTION
• Our Sql Delete Duplicates Reference