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