Re: is there a better way to do this?
Date: Wed, 20 Feb 2008 04:46:59 -0800 (PST)
Message-ID: <0823b1ae-48cb-481c-b2d4-3e007a3a948d@72g2000hsu.googlegroups.com>
On Feb 19, 6:00 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor..._at_acm.org> wrote:
> "Ben" <benal..._at_yahoo.com> wrote in message
>
> news:faba7a38-01a2-4ac1-8735-714e119c2edf_at_s19g2000prg.googlegroups.com...
>
>
>
>
>
> > 10.2.0.2 EE
>
> > Is there a more logical sql statement to get the same results than
> > what I am running here?
>
> > create table t (a number, b number);
>
> > insert into t values (1, 1);
> > insert into t values (1, 2);
> > insert into t values (2, 3);
> > insert into t values (3, 2);
> > insert into t values (4, 4);
> > insert into t values (4, 5);
> > insert into t values (5, 1);
> > insert into t values (6, 8);
> > commit;
>
> > select a.a, a.b
> > from t a, (
> > select b, count(distinct a)
> > from t
> > group by b
> > having count(distinct a) > 1) b
> > where a.b = b.b
>
> > A B C D
> > ---------- ---------- -------------------- --------------------
> > 5 1 a a
> > 1 1 a a
> > 3 2 a a
> > 1 2 a a
>
> > What I am wanting are the values for A where B is the same.
>
> Try:
>
> SELECT a, b
> FROM t r
> WHERE EXISTS
> (SELECT *
> FROM t
> WHERE b = r.b
> AND a <> r.a);
>
> a b
> ----------- -----------
> 1 1
> 1 2
> 3 2
> 5 1
>
> --
> David Portas- Hide quoted text -
>
> - Show quoted text -
Thanks yes, this does look like it is another way to get to what I am needing. I'll compare the two on my system to see which runs better. Received on Wed Feb 20 2008 - 06:46:59 CST