please dont rip this site

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 15:58, local time: 2024/3/28 01:49, owner: JMN-EFP-786,
TOP NEW HELP FIND: 
3.91.19.28: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://ecomorder.com/Techref/language/sql/tsqldups.htm"> Duplicates</A>

After you find an appropriate page, you are invited to your to this massmind site! (posts will be visible only to you before review) Just type a nice message (short messages are blocked as spam) in the box and press the Post button. (HTML welcomed, but not the <A tag: Instead, use the link box to link to another page. A tutorial is available Members can login to post directly, become page editors, and be credited for their posts.


Link? Put it here: 
if you want a response, please enter your email address: 
Attn spammers: All posts are reviewed before being made visible to anyone other than the poster.
Did you find what you needed?

 

Welcome to ecomorder.com!

 

Welcome to ecomorder.com!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  .