The following query gets all of the rows between LowerBound and HigherBound from the ordered result set in the innermost query. This only works with Oracle8i, release 8.1 and up (order by in sub-queries not recognized until then). I used the data dictionary table all_objects as an example, you can put your own query there.
SELECT *
FROM ( SELECT a.*, ROWNUM r
FROM ( SELECT *
FROM all_objects ao
WHERE ao.object_type = 'PACKAGE'
ORDER BY ao.object_name ) a
WHERE ROWNUM <= :HigerBound )
WHERE r >= :LowerBound
Then you may say how about this query:
SELECT *
FROM (SELECT ao.*, ROWNUM r
FROM all_objects ao
WHERE ao.object_type = 'PACKAGE'
ORDER BY ao.object_name ) a
WHERE a.r between :LowerBound and :HigerBound
Well, that works too, but may not be as efficient as the first one. For more detailed discussion, read here and here.
Possibly related:
- Give Me One Minute And I’ll Tell You If You Are Liberal Or Conservative
- SQL Developer and UTF8
- Back to basics: outer joins
- Back to basics: anti-joins and semi-joins
- Producing XML from SQL using cursor expressions
Tagged paging, query | Post a Comment


















nice query, helpful and time saving.
July 24th, 2005, at 12:34 pm #We found that style query to be so useful that we ended up creating a function that will wrap any SELECT statement stated a string, and return the result as an open cursor, with an OUT parameter that contains the total count that would be returned from the original cursor.
August 10th, 2005, at 9:48 am #