Sir, This sql worked. Can any body explain how this sql works ?
Ted Chyn (tedchyn_at_yahoo.com)
In article <tzrn3.544$bm.24829_at_dfiatx1-snr1.gtei.net>,
"Christopher Allen" <progplusSPAMBEGONE_at_gte.net> 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
> >
>
>
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Thu Jul 29 1999 - 22:07:33 CDT
