Re: Reducing hte number of SQL statements

[posted & mailed]

Senthil Kumar <skumar_at_scotch.cs.mci.com> wrote in article <slrn46nen8r.hat.skumar_at_scotch.mcit.com>...
> Assume there is some table T.
> For some select criteria, I only want
> the first 10 rows, however, I also want
> to find out the number of rows that match the
> select criteria.
>
> Is it possible to do this in one
> SQL statement?
>
> I know how to do this in 2 SQL statements.
>
> 1. select count(*) from T where ...
> 2. select ... from T where .. AND rownum <= 10
>
> thanks,
> Senthil
>

Hi,
the following query will return 10 rows with the total number of rows as the first column

select t1.numrow, t2.*
from (select count(*) as numrow from T where ...) t1, T t2 where .... and rownum <= 10

hth,
gert
--
reply address is altered to keep the spam down remove the nospamplease part to reply... Gert Rijs
gem at wirehub.nl
www.wirehub.nl/~gem Received on Fri Jun 05 1998 - 05:19:55 CDT