Re: auto increment in select clause

"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. Received on Fri Jun 07 2002 - 18:41:38 CEST