Select the first 150 rows, then the next 150 and so on?

Question

How can I select in oracle sql in a Table the first x rows, then the next x and so on? I know I could use TOP/LIMIT, then I get the first x

select a from b limit 150 => get the first 150 rows.

Edit: Why? I would like to copy the first 150 outputs into a file, then the next 150 into another file and so on...

Answer 1

LIMIT 150 or LIMIT 0,150 : first 150 rows

LIMIT 150,150 : next 150 rows

LIMIT 300,150 : next 150 rows

and so on

Answer 2

In Oracle you have the nice rownum: it is a pseudo column. It numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.

SELECT 
    a, b
FROM
    (SELECT rownum rn, a, b from table WHERE c=some_value ORDER BY some_column)
WHERE 
    rn BETWEEN 150 AND 300;

(thanks to @Mark Bannister)

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows.