Handling duplicates

[QuePID]

I have a large table with roughly thousands of duplicates in a field which should have been unique.

 

I need a way of creating a new table with all the duplicate records for later sorting.

 

I have a duplicate-free copy of the old table saved as a new table.

 

I have tried

CREATE TABLE iknew1 as SELECT iknew.*, COUNT(*) FROM iknew GROUP BY Accession HAVING COUNT(*) > 1

 

But this only copies one of the duplicated records not both, and I will need both records for comparison and to aid determining if the dupes are from data entry.

 

[Festy]

use 'SELECT DISTINCT......' query to fetch unique rows.

[kickstart]

Hi

 

I think what you are trying to get is every duplicate of duplicated records from the table but ignoring any record that isn't duplicated, with Accession being duplicated over multiple rows.

 

If so then something like this:-

 

SELECT a.*
FROM iknew a
INNER JOIN (SELECT Accession, COUNT(*) FROM iknew GROUP BY Accession HAVING COUNT(*) > 1) b ON a.Accession = b.Accession

 

All the best

 

Keith