Re: is there a better way to do this?
From: Walt <walt_askier_at_SHOESyahoo.com>
Date: Tue, 19 Feb 2008 16:52:55 -0500
Message-ID: <_cIuj.27199$6i4.17678@en-nntp-03.dc1.easynews.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.
Date: Tue, 19 Feb 2008 16:52:55 -0500
Message-ID: <_cIuj.27199$6i4.17678@en-nntp-03.dc1.easynews.com>
Ben wrote:
> On Feb 19, 3:32 pm, Ben <benal..._at_yahoo.com> wrote:
>> 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.
> > The table had other columns (C, and D) that I meant to remove from the > output for clarity. Instead I forgot to remove them and in turn made > it confusing.
You lost me when you made your table aliases the same as your column names. I don't have a clear idea what you're trying to do.
I think you might be after this:
select * from t t1, t t2
where t1.a=t2.b;
or maybe this:
select * from t where a=b;
//Walt Received on Tue Feb 19 2008 - 15:52:55 CST