Re: SQL statement question. Please Help ...

From: Christopher Allen <progplusSPAMBEGONE_at_gte.net>
Date: Tue, 27 Jul 1999 23:48:41 GMT
Message-ID: <tzrn3.544$bm.24829@dfiatx1-snr1.gtei.net>

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 Tue Jul 27 1999 - 18:48:41 CDT