Re: Limiting Returned Rows In Select Statement

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

>

>