Oracle

finding duplicate names

Please support our Oracle advertiser: 50% Off 6 Months Web Hosting from 1&1. The World's #1 Host!

•

Join Date: Jul 2007

Posts: 29

Reputation: creativehacker is an unknown quantity at this point

Solved Threads: 0

creativehacker creativehacker is offline

Offline

Light Poster

finding duplicate names

Jan 14th, 2009

I want to find duplicate names from my database I wrote the following query. But it is displaying more that existing records. I guess it is taking cartesian product.

select a.name from emp from emp a,emp b where a.name=b.name;

can anyone correct this query.
I want to display all the duplicate records having same names...

for example for the following table. I want to display result having same dname like
DEPTNO DNAME LOC
------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SALES hyd

output should be
30 sales chicago
50 sales hyd

Last edited by creativehacker; Jan 14th, 2009 at 12:42 pm.

•

Join Date: Feb 2007

Posts: 2,305

Reputation: debasisdas will become famous soon enough

Solved Threads: 147

debasisdas debasisdas is offline

Offline

Nearly a Posting Maven

Re: finding duplicate names

Jan 16th, 2009

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM dept WHERE d_name IN (SELECT d_name FROM dept GROUP BY d_name HAVING count(d_name)>1 )
select * from dept where d_name in (select d_name from dept group by d_name having count(d_name)>1 )

Last edited by peter_budo; Jan 18th, 2009 at 1:42 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

Share your Knowledge.

•

Join Date: Mar 2009

Posts: 9

Reputation: mir77 is an unknown quantity at this point

Solved Threads: 0

mir77

Offline

Newbie Poster

Re: finding duplicate names

Mar 4th, 2009

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT count(deptno), dname
FROM dept
GROUP BY dname
HAVING count(deptno)>1
select count(deptno), dname
from dept
group by dname
having count(deptno)>1

Last edited by peter_budo; Mar 4th, 2009 at 7:24 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.