Re: auto increment in select clause

From: Mikito Harakiri <mikharakiri_at_yahoo.com>
Date: 12 Jun 2002 20:33:50 -0700
Message-ID: <bdf69bdf.0206121933.28936644@posting.google.com>

"Bob Badour" <bbadour_at_golden.net> wrote in message news:<Cq5M8.25136$Ey5.201345111_at_radon.golden.net>...

> "Samuel Tribehou" <s.tribehou_at_wanadoo.fr> wrote

> >
> > > select count(*) as inc, t1.ref as ref
> > > from atable t1, atable t2
> > > where t2.ref <= t1.ref
> > > group by t1.ref
> > > ;
> >
> > Thanx alot, it works nicely.
> > My form can now be populated in one sec less than before :)
> > (I had to fill a recordset, then iterate throught it searching for the

>  right

> > value,
> > and finally return the position of the row in the recorset...)

> 
> I should warn you that the above does not work correctly when there might be
> duplicate 'ref' values. In general, one should do the '<' comparison on the
> ranking attribute and the '=' comparison on a candidate key.
> 
> One can turn the above into a quota query (ie. top N items) by adding the
> appropriate HAVING clause.

Indeed:

sql>select count(t2.deptno) inc, t1.deptno as ref   2 from emp t1, emp t2
  3 where t2.deptno <= t1.deptno
  4 group by t1.deptno;

       INC REF
---------- ----------

        84         30
        40         20
         9         10

However:

sql>select count(distinct t2.deptno) inc, t1.deptno as ref   2 from emp t1, emp t2
  3 where t2.deptno <= t1.deptno
  4 group by t1.deptno;

       INC REF
---------- ----------

         3         30
         2         20
         1         10

Duplicates handling needs a PK:

sqlr>select

  2     (select count(1) from emp ee where ee.empno <= e.empno) inc
  3     , deptno

  4 from emp e;

       INC DEPTNO
---------- ----------

         1         20
         2         30
         3         30
         4         20
         5         30
         6         30
         7         10
         8         20
         9         10
        10         30
        11         20
        12         30
        13         20
        14         10

Received on Wed Jun 12 2002 - 22:33:50 CDT