report/remove duplicate records

To remove duplicate records from a table. What field(s) determine a duplicate, such as

SSN, or SSN and code assignment. In my example shown I use SSN and a code that desinates a group assigned to. The SSN can have more then one unique code assignment.

First verify duplicates with below command

select ssn,p_code count(*) from p_code group by ssn,p_code having count(*) >1
The COUNT function produces a total of all records, but adding the GROUP BY there is a individual line for each unquie SSN+Code. Adding the having clause > 1 shows only the duplicates.


Next make a file of all records without the dupiclates with this command

select * , count(distinct ssn+p_code) from p_code group by ssn,p_code into table temp
DISTINCT produces only one for each duplicate record and for each non-duplicate. The temp table produced will have all fields plus a field added that shows the count.

Now zap the p_code table, then append from the file temp

The extra field in the temp gets drouped when you append.