ON 20020124@8:57:03 PM at page:
http://techref.massmind.org/techref/language/sql/transact.htm
JMN-EFP-786 James Newton added 'Says
http://techref.massmind.org/techref/language/sql/tsqldups.htm
Duplicates'

ON 20020124@8:58:10 PM at page:
http://techref.massmind.org/techref/language/sql/tsqldups.htm
JMN-EFP-786 James Newton added 'Says

<H3>Finding Duplicates</H3>
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.
<PRE>
       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
</PRE>
<h3>Removing Duplicates</h3>
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.
<pre>
       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 
             )
</PRE>'

ON 20020124@10:15:23 PM at page:
http://techref.massmind.org/techref/language/sql/tsqldups.htm
JMN-EFP-786 James Newton added 'Says

This is general discussion. You should translate it to your case by yourself.
<PRE>
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 )
<PRE>
--<BR>
Ivan Arjentinski

'

ON 20020124@10:15:28 PM at page:
http://techref.massmind.org/techref/language/sql/tsqldups.htm
JMN-EFP-786 James Newton added 'Says

This is general discussion. You should translate it to your case by yourself.
<PRE>
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 )
<PRE>
--<BR>
Ivan Arjentinski

'

ON 20020124@11:00:23 PM at page:
http://techref.massmind.org/techref/language/sql/tsqldups.htm
JMN-EFP-786 James Newton edited the page