Re: Finding Duplicates in a table

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Thu, 24 Jun 1999 15:09:46 +0200
Message-ID: <7ktakd$r1e$1@news3.saix.net>

smfrank_at_my-deja.com wrote in message <7ks4f3$ove$1_at_nnrp1.deja.com>...
>Could some one please provide me with a SQL statement that
>will allow me to list duplicate(non-distinct) records in a table?

If I understand your question correctly:

SELECT

 t1.rownum "original",
 t2.rownum "duplicate",
 t1.*

FROM foo t1, foo t2
WHERE t1.unique_key = t2.unique_key
AND t2.rowid > t1.rowid

In simple English, you join the table with itself. The join criteria is the unique identifier (which of course has been "violated" in the table). In order to make sure that you are not joining the same physical rows with one another, you add the rowid clause to the join criteria. T1 will thus return the 'original rows' (the rows first inserted) and T2 will contain the duplicate rows.

regards,
Billy Received on Thu Jun 24 1999 - 08:09:46 CDT