Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to limit the number of rows returned in a select statement

Re: How to limit the number of rows returned in a select statement

From: Scott Maxson <scottm_at_quando.com>
Date: 1997/06/03
Message-ID: <33948D69.15D@quando.com>#1/1

Stefan Fallet wrote:
>
> Hi does anyone know how to limit he number of rows returned by a select.
>
> I tryed using rownum. but this does not work with an order by.
>
> my select: select X,Y from table_a order by Y.
>
> I only want the first 15 rows after the sort.
> Using rownum <=15 brings back the first 15 rows in the table.
>
> Need help fast
>
> Thanks
>
> Stefan Fallet
>
> E-Mail: stfallet_at_fallet.com

Stefan,

Sorry, I don't know a way to make this work with any arbitrary ORDER BY clause in your SELECT - as I understand it, ROWNUM's get assigned before the sort (which you've discovered). However, if your table has an index on column Y, and this index is used for retrieval (you could use an optimizer hint to ensure this) then records are fetched in order based on this index; you should be able to eliminate the ORDER BY clause from your statement. There are a few pitfalls; see Gurry and Corrigan, "Oracle Performance Tuning", pp 199-201 for more on this. Probably someone else here knows more than I do about the subject, too ...?

Otherwise I think you'll have to use something other than pure SQL, like PL/SQL or Pro*C, to get what you want.

Hope this helps,

Scott Maxson
scottm_at_quando.com Received on Tue Jun 03 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US