Re: Please help on SQL
From: Andrew Barnett <nobody_at_spamandeggs.bp.com>
Date: 1998/03/25
Message-ID: <01bd583c$f3def840$8c0564a1_at_azmelw1358.mel.az.bp.com>#1/1
Date: 1998/03/25
Message-ID: <01bd583c$f3def840$8c0564a1_at_azmelw1358.mel.az.bp.com>#1/1
select a, b from foo having count(*) > 1 group by a, b;
-- Andrew - Wizzard barnetaj_at_bp.com Shetal Sheth <sheths_at_bobcat.ent.ohiou.edu> wrote in article <01bd582b$f2b79f00$83819f96_at_dunt0538>...Received on Wed Mar 25 1998 - 00:00:00 CET
> 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
>