cancel
Showing results for 
Search instead for 
Did you mean: 

duplicate record sql

Edgar_8
Regular Advisor

duplicate record sql

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!
8 REPLIES
Elmar P. Kolkman
Honored Contributor

Re: duplicate record sql

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.
Every problem has at least one solution. Only some solutions are harder to find.
Elmar P. Kolkman
Honored Contributor

Re: duplicate record sql

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.
Every problem has at least one solution. Only some solutions are harder to find.
Sanjay Kumar Suri
Honored Contributor

Re: duplicate record sql

Following will work in Oracle:

select from
group by
having count() > 1

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Michael Schulte zur Sur
Honored Contributor

Re: duplicate record sql

Hi,

try this:

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

greetings,

Michael
Brian Crabtree
Honored Contributor

Re: duplicate record sql

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
Honored Contributor

Re: duplicate record sql

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Sanjay Kumar Suri
Honored Contributor

Re: duplicate record sql

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
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Volker Borowski
Honored Contributor

Re: duplicate record sql

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