Re: duplicates

==========Chris Woodyard, 8/10/95========== michel.lalonde_at_sit.ulaval.ca (Michel Lalonde) wrote:
>
> In article <1995Aug1.115212.57550_at_ucl.ac.uk> ucfaabe_at_ucl.ac.uk (Mr Angus
 Beare) writes:
> >From: ucfaabe_at_ucl.ac.uk (Mr Angus Beare)
> >Subject: duplicates
> >Summary: how do I find duplicates in large oracle tables?
> >Date: Tue, 1 Aug 1995 11:52:12 GMT
> >Keywords: how to find duplicate recs
>
>
> >I have a table that I want to create a unique index for
> >but I can't because of duplicates. Oracle won't tell
> >me where they are.
 

> >Does anyone know a quick way to find duplicate records?
 

> >thanks for any suggestions
 

> >Gus
 

> >--
> >Angus Beare
>
> Add a unique constraint with the "exceptions into" option (the unique index
 

> will be created automaticaly). The duplicates will be listed in the
 exceptions
> table.
>
> Michel Lalonde.
>

Another possible solution is the following script which will list the values of the columns that are duplicates. This has proven useful to me many times.

select column_name,count(*) from owner.table

group by column_name having count(*) > 1           
/                                                     

Chris Woodyard

Yet another possible solution would be:

Select a.column_name, count(*)
from table_name a, table_name b
where a.column_name = b.column_name
and a.rowid <> b.rowid
groupby a.column_name

By using aliases you can compare the table to itself. The select will return the duplicate values and how many times the value is present. Received on Mon Aug 14 1995 - 00:00:00 CEST