duplicate record sql

[Edgar_8]

Hi,

We have a 9i DB & would like to search for duplicate records on a specific column. Does anypne know
what the sql statement should look like?

Thanks in advance!

[Elmar P. Kolkman]

select from where count() > 1 group by

This should do the trick. Of course this can be expanded to whatever you like, or used in a subselect.

[Elmar P. Kolkman]

Sorry, I'm not sure it works in Oracle, but at least doesn't work in MySQL. You could change it to:

create view cnt_view as select ,count(specific) cnt from group by ;
select * from cnt_view where cnt > 1;

Or something like that.

[Sanjay Kumar Suri]

Following will work in Oracle:

select from
group by
having count() > 1

sks

[Michael Schulte zur Sur]

Hi,

try this:

select col from table group by col having count(*) > 1;

greetings,

Michael

[Brian Crabtree]

This is one that I picked up from Metalink, that I just love for this:

delete from tableA
where rowid in
(select rowid from tableA minus
select max(rowid) from table group by columnA)

You will need to change tableA and columnA.

Thanks,

Brian

[Yogeeraj_1]

hi,

Also, consider the following example:

yd@MYDB.MU> create table t1 ( c1 int, c2 int, c3 char(1) );

Table created.

yd@MYDB.MU> insert into t1 values ( 1, 50, 'a' );

1 row created.

yd@MYDB.MU> insert into t1 values ( 1, 50, 'b' );

1 row created.

yd@MYDB.MU> insert into t1 values ( 2, 89, 'x' );

1 row created.

yd@MYDB.MU> insert into t1 values ( 2, 89, 'y' );

1 row created.

yd@MYDB.MU> insert into t1 values ( 2, 89, 'z' );

1 row created.

yd@MYDB.MU> delete from T1
2 where rowid <> ( select max(rowid)
3 from t1 b
4 where b.c1 = t1.c1
5 and b.c2 = t1.c2 )
6 /

3 rows deleted.

yd@MYDB.MU> select * from t1;

C1 C2 C
---------- ---------- -
1 50 b
2 89 z


I use it to delete duplicates.

hope this helps too!

regards
Yogeeraj

[Sanjay Kumar Suri]

Hello Edqar

Did the following work:
select from
group by
having count() > 1

One has to ensure that all columns given in group by must also be included in the select clause.

Other columns can only be included in the select if they are used with grouping functions such as max, min, sum, count etc.

sks

[Volker Borowski]

Hi,

since you gave only 5 or 6 points to the group & having statements (which are correct as I think), could it be, that you ar not looking for these Values, but the entire records ?

Like this ?

select * from table where specific_column
in
(
select specific_column
from table
group by specific_column
having count(*) > 1 )
order by specific_column;

Do not know if this helps
Volker