Re: auto increment in select clause

From: Bob Badour <bbadour_at_golden.net>
Date: Thu, 13 Jun 2002 01:45:55 -0400
Message-ID: <_lWN8.185$9R7.32950363_at_radon.golden.net>

"Mikito Harakiri" <mikharakiri_at_yahoo.com> wrote in message news: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.
>

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

The above SQL statement has a bug in that the '<' comparison is not done on the ranking attribute. I would have written it as:

select count(*) as inc, e1.deptno as ref from emp e1, emp e2
where e2.deptno < e1.deptno
or e2.empno = e1.empno
group by e1.deptno
;

A quota query (Top N) would require a having clause (Top N reverses the inequality comparision; Bottom N would still use '<'):

select e1.*
from emp e1, emp e1
where e2.deptno > e1.deptno

   or e2.empno = e1.empno
group by e1.*
having count(*) <= N
;

Replace N with the appropriate number of rows to return. ie. replace N with 10 for a "Top 10 employees by department number" query. Of course, a more practical example would probably replace deptno with sales or age or salary or some such. I suspect most dialects of SQL will require one to expand the e1.* in the "group by" clause.

Isn't SQL ridiculous? Received on Thu Jun 13 2002 - 07:45:55 CEST