From: mcstockX@Xenquery   
      
   "Michael Hill"  wrote in message   
   news:404926C0.64BFB1D7@ram.lmtas.lmco.com...   
   |   
   |   
   | "Mark C. Stock" wrote:   
   | >   
   | > "Michael Hill"  wrote in message   
   | > news:40479F09.C62F9BE4@ram.lmtas.lmco.com...   
   | > | I have some query like:   
   | > |   
   | > | select my_field from my_table   
   | > |   
   | > | and there are 500000 rows in the table, how do I tell Oracle to   
   display   
   | > | the next 33?   
   | > |   
   | > | i tried select my_field from my_table next 33 and it doesn't like it.   
   | > |   
   | > | Mike   
   | >   
   | > are you familiar with the WHERE clause?   
   | >   
   |   
   | yes and so using the where clause gets me from 500000 rows to 25000   
   |   
   | > what version of oracle are you using?   
   | >   
   |   
   | Dont know for sure at least 8. something I think.   
   |   
   | > what is your interface (tool)?   
   | >   
   |   
   | I am using perl dbi and so I'd like to limit the number number of   
   | records using the query statement. Some tools do that for you as does   
   | coldfusion.   
   |   
   | > ;-{ mcs   
      
   to get version:   
      
   select * From v$version   
      
      
   to return a 'pageful' of rows, do something like this:   
      
   -- filter by rownum range (not possible in query that selects the rownum)   
   select uu.*   
   from       (   
      -- add rownum after the order by is performed (assigned in order row is   
   processed)   
      select rownum as therownum, u.*   
      from (   
              -- the real query   
              select username   
              from   all_users   
              order  by username   
              ) u   
      where rownum <= :endrec   
      ) uu   
   where uu.therownum >= :startrec   
      
      
   it would be tempting to try this:   
      
       select username   
       from  all_users   
       order by username   
       where rownum between :startrec and :endrec   
      
   but that doesn't work because a) rownum is assigned prior to the sort and b)   
   a where clause predicate cannot filter rows based on a (locally assigned)   
   rownum being greater than a known value, because the rownum starts at 1 and   
   is only incremented as rows are added to the resultset -- the first row   
   returned would have a rownum of 1, if it is rejected, rownum does not   
   increment, so the 2nd row examined would have a rownum of 1, etc.   
      
   so, the select with order by is used as an inline view (from-clause   
   subquery) and gets the rownum assigned as rows are retrieved (ordered) from   
   the inline view, stopping at the specified endrec. this is then used as an   
   inline view to another query that discards all rows prior to the specified   
   startrec   
      
   -{ mcs   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)