Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Please help on SQL

Re: Please help on SQL

From: Daniel Perez <perezda_at_cowen.com>
Date: 1998/03/25
Message-ID: <35197F7B.1AE6BA0B@cowen.com>#1/1

Your query was fine, just add a distinct:

Select DISTINCT pp.a, pp.b from foo pp where (select count(*) from foo yy where yy.a = pp.a and yy.b = pp.b ) > 1

or try another way which works also:

select a,b
from foo
group by a,b
having count(*) > 1

Shetal Sheth wrote:

> Hi Gurus,
>
> I am looking for the efficient way to construct SQL statement for following
>
> requirement, and I seem to be running out of ideas.
>
> following is the table and some sample data,
>
> Table foo : Column a Column b Column c Column d
> 111 222 0000 999
> 111 223 0000 4000
> 111 223 4001 5000
> 222 333 3333 4444
> 333 444 0000 3000
> 333 444 3001 5000
>
> Basically, I want to retrieve column A and Column B from all those records
> of
> table foo where there is more than one entry for each column A and column B
>
> pair. Having said that the result of the mentioned query should return
> following data from the table foo.
>
> a b
> -- --
> 111 223
> 333 444
>
> In other words, I want something like this,
> (Following is **NOT** a SQL statement, I just used to convey the idea)
>
> Select pp.a, pp.b from foo pp where
> (select count(*) from foo yy where yy.a = pp.a and yy.b = pp.b ) > 1;
>
> Thanks in advance,
> --
> Shetal Sheth
> sheths_at_bobcat.ent.ohiou.edu
Received on Wed Mar 25 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US