Duplicates

TransactSQL: Finding Duplicates

Finding Duplicates

If we group records together by certain identifying fields we can then use a Count function to extract those that are duplicated. This query utilizes a sub-query and, based on their first and last names, returns a list of DelegateIDs for those delegates that have duplicate records.


       SELECT Delegate.DelegateID, Delegate.FirstName, Delegate.LastName 
       FROM Delegate INNER JOIN 
             (SELECT FirstName, LastName 
                    FROM Delegate 
                    GROUP BY FirstName, LastName 
                    HAVING Count(DelegateID) > 1 
             )     AS Duplicates 
             ON Delegate.FirstName = Duplicates.FirstName 
             AND Delegate.LastName = Duplicates.LastName

Removing Duplicates

The following query removes all duplicates from the delegate table, leaving only the originals of the duplicated record. Here, we define a record as having a duplicate if there is another record with matching FirstName and LastName fields. This query relies on a view being set up that returns a list of duplicate delegates. The view is called DupDels.


       DELETE FROM Delegate
       WHERE DelegateID =
       ANY ( SELECT DupDels.DelegateID 
             FROM DupDels LEFT JOIN 
                    (SELECT Min(DelegateID) AS DelegateID, FirstName, LastName 
                          FROM DupDels GROUP BY FirstName, LastName) AS FirstDup 
                    ON DupDels.DelegateID = FirstDup.DelegateID 
             WHERE FirstDup.DelegateID IS NULL 
             )

This is general discussion. You should translate it to your case by yourself.


CREATE TABLE Dups ( ID INT IDENTITY(1,1) PRIMARY KEY, A VARCHAR(30), B VARCHAR(30), C VARCHAR(30) )
go INSERT INTO Dups(A,B,C) VALUES ('hi','there','all') INSERT INTO Dups(A,B,C) VALUES
('hi','there','all') INSERT INTO Dups(A,B,C) VALUES ('bye','from','me') INSERT INTO Dups(A,B,C)
VALUES ('repeated','three','times') INSERT INTO Dups(A,B,C) VALUES ('repeated','three','times')
INSERT INTO Dups(A,B,C) VALUES ('repeated','three','times')

-- Duplicate rows - one from each group SELECT * FROM Dups T WHERE ID > ( SELECT MAX(ID) FROM Dups I
WHERE I.A = T.A AND I.B = T.B AND I.C = T.C AND I.ID <> T.ID )

-- Non-duplicated view - one row from each group SELECT * FROM Dups T WHERE ID >= ALL ( SELECT ID
FROM Dups I WHERE I.A = T.A AND I.B = T.B AND I.C = T.C )

-- Rows to be deleted - usefull for COUNT(*) and DELETE 
SELECT * FROM Dups T 
 WHERE ID < ( 
  SELECT MAX(ID) FROM Dups I 
   WHERE I.A = T.A AND I.B = T.B AND I.C = T.C AND I.ID <> T.ID 
  )

-- Delete the duplicates 
DELETE Dups 
 WHERE ID < ( 
  SELECT MAX(ID) FROM Dups I 
   WHERE I.A = Dups.A AND I.B = Dups.B AND I.C = Dups.C AND I.ID <> Dups.ID 
  )

--
Ivan Arjentinski

file: /Techref/language/sql/tsqldups.htm, 3KB, , updated: 2016/12/9 14:58, local time: 2024/11/28 13:14, owner: JMN-EFP-786,

^{18.117.105.230:LOG IN}

©2024 These pages are served without commercial sponsorship. (No popup ads, etc...).Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE. Questions?

Please DO link to this page! Digg it! / MAKE!

<A HREF="http://sxlist.com/techref/language/sql/tsqldups.htm"> Duplicates</A>

Did you find what you needed?

Welcome to sxlist.com!

Site supported by
sales, advertizing,
& kind contributors
just like you!
Please don't rip/copy
(here's why
Copies of the site on CD
are available at minimal cost.