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_at_posting.google.com>
> >
> > > 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
> > value,
> > and finally return the position of the row in the recorset...)
4 from emp e;
Date: 12 Jun 2002 20:33:50 -0700
Message-ID: <bdf69bdf.0206121933.28936644_at_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 Thu Jun 13 2002 - 05:33:50 CEST