CODEWITHSUNDEEP
sqloracle-databaseselect
llcf
llcf

Reputation: 21

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

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

Upvotes: 2

Views: 8785

Answers (3)

Dino
Dino

Reputation: 31

You could use ROW_NUMBER() function... for example

SELECT * 
FROM ( SELECT A.*, ROW_NUMBER( ) OVER (ORDER BY MYFIELD) AS MYROW FROM MYTABLE A ) 
WHERE MYROW < X

SELECT * 
FROM ( SELECT A.*, ROW_NUMBER( ) OVER (ORDER BY MYFIELD) AS MYROW FROM MYTABLE A ) 
WHERE MYROW BETWEEN X AND Y

SELECT * 
FROM ( SELECT A.*, ROW_NUMBER( ) OVER (ORDER BY MYFIELD) AS MYROW FROM MYTABLE A ) 
WHERE MYROW BETWEEN Y AND Z

Upvotes: 3

Karl Bartel
Karl Bartel

Reputation: 3434

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.

Upvotes: 0

Thilo
Thilo

Reputation: 262860

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

Upvotes: 13

Related Questions