Re: is there a better way to do this?

"Ben" <benalvey_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