How to extract unique record to a new table and the duplicate in another new table ?

[akino25]

Hi,

I have this database that do contains duplicate record (not duplicate field).

What I'm trying to do here is to extract record that is unique to a new table
And those record that were duplicate into a new table.



the *Distinct that we used in sql will not work because it is not what I wanted. using distinc will give me unique record of the whole database.

say i have this database that contain an ID:

2525
2658
2658
2658
2525
3678

so what i want is to extract unique record to a unique table and those that contain duplicate in another table.

so the 3678 will be put in a new table and the 2525 and 2658 in a new table
That way it will help me see which file will have duplicate

[June7]

Why do you need to put data in other tables? An aggregate query should be able to present the duplicates/nonduplicates.

SELECT ID, Count(ID) As CountID FROM tablename GROUP BY ID;

SELECT ID, Count(ID) As CountID FROM tablename GROUP BY ID HAVING Count(ID) = 1;

SELECT ID, Count(ID) As CountID FROM tablename GROUP BY ID HAVING Count(ID) > 1;