Re: SQL statement question. Please Help ...

Hello,

    How about if the table AAA has one additional column, PKAAA, as the primary key of table AAA as follows:

            PKAAA        number;
            A        number
            B        varchar2(10)

            PKAAA        A            B
            -------        --------      ------------
[Quoted]              1                    1            AAA
             2                    1            AAA
             3                    1            AAA
             4                    2            BBB
             5                    2            BBB
             6                    3            CCC

            And the results look like:

            PKAAA        A            B              C
            -------        --------      ------       ---
             1                    1            AAA        1
             2                    1            AAA        2
             3                    1            AAA        3
             4                    2            BBB         1
             5                    2            BBB         2
             6                    3            CCC        1

Thanks,
Jimmy

Christopher Allen wrote:

> A nice piece of code. I believe the table names need to be corrected,
> however:
>
> select a, b, count(*) c from
> ( select t.a, t.b, t0.rowid r
> from t, t t0
> where t.a = t0.a and
> t.b = t0.b and
> t.rowid <= t0.rowid
> )
> group by a, b, r
>
> $.02,
> Christopher Allen
>
> Anna Sotnichenko <annasony_at_home.com> wrote in message
> news:379E22C5.11148F13_at_home.com...
> > If there is no primary key on a table it is always possible to use ROWID
> > instead
> > Try this one (AAA is a table name)
> >
> > select a, b, count(*) c from
> > ( select t.a, t.b, t0.rowid r
> > from aaa t, aaa t0
> > where t.a = t0.a and
> > t.b = t0.b and
> > t.rowid <= t0.rowid
> > )
> > group by a, b, r;
> >
> >
> >
> > Jimmy wrote:
> >
> > > Hello all,
> > >
> > > I have a table with the following definition: (assume no P.K.)
> > >
> > > A number
> > > B varchar2(10)
> > >
> > > And this table has the following rows:
> > >
> > > A B
> > > ------- --------
> > > 1 AAA
> > > 1 AAA
> > > 1 AAA
> > > 2 BBB
> > > 2 BBB
> > > 3 CCC
> > >
> > > Can I obtain the result by a single SQL statement as follows:
> > >
> > > A B C
> > > ----- -------- ----------
> > > 1 AAA 1
> > > 1 AAA 2
> > > 1 AAA 3
> > > 2 BBB 1
> > > 2 BBB 2
> > > 3 CCC 1
> > >
> > > i.e. if A and B have the same values, C should be set to number of
> > > count of the pair of A and B.
> > >
> > > Thanks,
> > > Jimmy
> >
Received on Fri Jul 30 1999 - 05:45:39 CEST