Re: Please help on SQL

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 CET