Re: Limit number of records returned in a query in 8i.

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/07/03
Message-ID: <8jq4ot$glj$1@nnrp1.deja.com>#1/1

In article <396074E9.948A30E9_at_mars-systems.com>,   Joe Condle <condle_at_mars-systems.com> wrote:
> I hope I can explain this right. I want to limit the amount of output
> returned from a query. I know I can say where rownum < 100. And it
> will only return 100 rows. Is there another way of doing this? Is
> there any enhancements to 8i that will allow more options?
> Thanks in advance
> -
> Joseph P. Condle condle_at_mars-systems.com
> Systems Analyst II Office
> 412-473-6542
> Medical ARchival Systems, Inc. (MARS) Pager 412-572-9490
> 1370 Beulah Road Pgh. Pa. 15235 Fax 412-473-6538
>

the way to do it is still with rownum. In 8i, you can do any query with it:

select *
  from ( select rownum r, a.*

           from ( <YOUR QUERY GOES HERE, ORDER BYS and ALL ) A
          where rownum <= :MAXROW_TO_FETCH )

 where r >= :MIN_ROW_TO_FETCH;

in that fashion you can get rows N through M or just use the inner query to get rows less then M

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Mon Jul 03 2000 - 00:00:00 CDT