topic duplicate record sql in Operating System - HP-UX

duplicate record sql

Edgar_8 — Thu, 08 Jan 2004 07:08:15 GMT

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!

Re: duplicate record sql

Elmar P. Kolkman — Thu, 08 Jan 2004 07:29:26 GMT

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.

Re: duplicate record sql

Elmar P. Kolkman — Thu, 08 Jan 2004 07:42:14 GMT

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.

Re: duplicate record sql

Sanjay Kumar Suri — Thu, 08 Jan 2004 07:51:20 GMT

Following will work in Oracle:

select from
group by
having count() > 1

sks

Re: duplicate record sql

Michael Schulte zur Sur — Thu, 08 Jan 2004 07:54:41 GMT

Hi,

try this:

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

greetings,

Michael

Re: duplicate record sql

Brian Crabtree — Thu, 08 Jan 2004 15:55:15 GMT

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

Re: duplicate record sql

Yogeeraj_1 — Thu, 08 Jan 2004 23:54:24 GMT

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

Re: duplicate record sql

Sanjay Kumar Suri — Thu, 08 Jan 2004 23:55:48 GMT

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

Re: duplicate record sql

Volker Borowski — Fri, 09 Jan 2004 15:03:49 GMT

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