Re: is there a better way to do this?

On Feb 20, 12:10 pm, Ben <benal..._at_yahoo.com> wrote:
> On Feb 20, 8:30 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On Feb 20, 6:44 am, Ben <benal..._at_yahoo.com> wrote:
>
> > > On Feb 19, 5:04 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > > On Feb 19, 2: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.
>
> > > > What, exactly, does that last sentence mean?
>
> > > > David Fitzjarrell- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > in the original post my table definition doesn't have a column C or D
> > > but the output shows them. I removed those columns from my post to try
> > > to avoid confusion and forgot to remove them from the output of the
> > > query.- Hide quoted text -
>
> > > - Show quoted text -
>
> > You need to read the text which was referenced, and it wasn't your
> > tacked-on follow-up.  Apparently David Portas could decipher this and
> > provide a reasonable alternative.
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> "What I am wanting are the values for A where B is the same."
>
> yes, I can see where it is confusing.
>
> Looking at the dataset that I created in the insert statements, you'll
> see all the values for column B. I want a listing of those records
> with duplicate B values. For this dataset the values of B that have
> duplicates are 1 and 2. I would want all records that have either a 1
> or 2 for B.  I needed a sql statement to return that information
> without already knowing that 1 and 2 were the values that had
> duplicates.- Hide quoted text -
>
> - Show quoted text -

SQL> select a, b
  2 from t
  3 where b in
  4 (select b from t group by b having count(*) > 1)   5 /

         A B
---------- ----------

         5          1
         1          1
         3          2
         1          2

SQL> David Fitzjarrell Received on Wed Feb 20 2008 - 14:30:42 CST