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

 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 
 )

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 
 )

file: /Techref/language/sql/tsqldups.htm, 3KB, , updated: 2016年12月9日 15:58, local time: 2025年10月4日 04:11, owner: JMN-EFP-786,

^{TOP NEW HELP FIND:}

©2025 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?
<A HREF="http://massmind.org/techref/language/sql/tsqldups.htm"> Duplicates</A>

Did you find what you needed?

• "No. I'm looking for: "
• "No. Take me to the search page."
• "No. Take me to the top so I can drill down by catagory"
• "No. I'm willing to pay for help, please refer me to a qualified consultant"

Welcome to massmind.org!

Welcome to massmind.org!