Re: Limiting Returned Rows In Select Statement

Oops! The approach below doesn't work with ORDER BY because it cannot be included within the subquery.

Any other thoughts on how one can accomplish the effect of adding WHERE ROWNUM < n except that the rows are limited *after* ordering occurs?

Thanks,

--
Randy Baker (remove Z from address in email replies)

Randy Baker wrote in message <6d6kfg$ik8_at_chile.earthlink.net>...


>This is rather clever. I have a couple of questions:

>

>1. How much additional performance penalty is imposed by using a nested

>select. I'd assume that this really only requires additional scan of the

>inner result set?

>

>2. Anyone know of a better way to do this? SQL Server has SET ROWCOUNT,

>which limits the final result set size but is applied *after* any ORDER BY

>clause. This is a critical distinction, as &deity. only knows what rows you

>will get in Oracle using ROWNUM < n without the nested approach shown

 below.


>

>The problem we have is that we use the ODBC cursor library with multiple

>open result sets.  Under the convers, the cursor library maintains only 1

>active connection at a time, so if you open 2 queries, the cursor library

>will the fetch all the rows in the first result set before beginning on the

>second, which means you potentially get megabytes of data (e.g. entire

>tables. Yikes!!!) copied to your machine that you may never look at.

>

>(I know, this shouldn't happen in a properly designed application, but

>Microsoft Access lets you get away with murder, and changes take time 8-)

>

>Thanks,

>

>--

>Randy Baker (remove Z from address in email replies)

>

>Richard Hoffbeck wrote in message ...

>>[This followup was posted to comp.databases.oracle.server and a copy was

>>sent to the cited author.]

>>

>>In article <34f3098c.85067550_at_ntserv02>, matthew_at_nospam.mattshouse.com

>>says...

>>> OK, I now understand what you're saying.  But how do I get the next

>>> 15?  Is this legal?

>>>

>>> select * from Imagedata where RowNum > 15 and RowNum < 31

>>>

>>> I can't get this one to work.

>>

>>This can get messy and the only way I've found to do it is to use

>>an embedded view, i.e. something like:

>>

>>   SELECT *

>>     FROM ( SELECT ID,

>>                   NAME,

>>                   STREET,

>>              CITY,

>>                   STATE,

>>                   rownum R

>>             FROM  mytable

>>          )

>>    WHERE R > 15

>>      AND R <= 30

>>

>>And in the inner view, you do have to explicitly list all of the fields

>>that you want to select.  SELECT *, rownum R doesn't work.

>>

>>Hope it helps!

>>

>>--rick

>>

>>

>

>