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...

Solution 1

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.

Solution 2

LIMIT 150 or LIMIT 0,150 : first 150 rows

LIMIT 150,150 : next 150 rows

LIMIT 300,150 : next 150 rows

and so on

Solution 3

I assume you're trying to do pagination, if so you can do it like this:

Let pageSize be 150

SELECT * FROM
(
    SELECT a.*, rownum r__
    FROM
    (
        SELECT * FROM tblName c
    ) a
    WHERE rownum < ((pageNumber * pageSize) + 1 )
)
WHERE r__ >= (((pageNumber-1) * pageSize) + 1)