Re: Finding duplicate rows in a table

chris <x_at_worldnet.att.net> asked: >>
How can I find the rows in the original table that have duplicate primary key information ? <<

There are several variations, but basically you do a outer self-join or just a self-join like:

select a.rowid, a.key_col_one, a.key_col_two, b.rowid from the_table a, the_table b
where a.key_col_one = b.key_col_one

and        a.key_col_two  =  b.key_col_two
and        a.rowid             !=  b.rowid

This version does not use an outer join and should result in both the original row and the duplicate row appearing so they can be cross referenced. This method does require the use of the Oracle (or similiar) provided psuedo column, rowid. There are other ways to write this, but I can not remember off the top of my head. Someone else will probably post the outer join to do this.     

Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Mon Sep 08 1997 - 00:00:00 CDT