sql (oracle) to select the first 10 records, then the next 10, and so on

Question

I figure I might just be missing some obvious syntax but what is the sql (oracle) to select the first 10 records, then the next 10, and so on?

I tried using rownum but can seem to get rownum > X and rownum < Y to work.

llcf

Answer 1

There is only a rather convoluted way to do this, which is a real pain with Oracle. They should just implement a LIMIT/OFFSET clause...

The rownum gets assigned after the row has been selected by the where clause, so that a rownum must always start with 1. where rownum > x will always evaluate to false.

Also, rownum gets assigned before sorting is done, so the rownum will not be in the same order as your order by says.

You can get around both problems with a subselect:

 select a,b,c, rn from 
    ( select a,b,c, rownum rn from 
         ( select a,b,c from the_table where x = ? order by c)
      where rownum < Y)
  where rn > X

If you do not need to sort (but only then), you can simplify to

 select a,b,c, rn from
     ( select a,b,c, rownum rn from the_table where rownum < Y )
   where rn > X

Answer 2

You could also select all rows, and only fetch 10 at a time. This works only if you can keep the cursor between the fetches, of course.