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

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

select a from b limit 150

=> get the first 150 lines.

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


source to share

6 answers

In Oracle, you have a good one rownum

: it's a pseudo-column. It displays the records in the result set. The first record that matches the where criteria in the select clause is specified rownum=1

, and each subsequent record that matches the same criteria is incremented rownum


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


(thanks @Mark Bannister)

If you insert an ORDER BY clause in a subquery and place a ROWNUM clause in the top level query, then you can enforce the ROWNUM clause after the rows have been ordered.




or LIMIT 0,150

: first 150 lines

LIMIT 150,150

: next 150 lines

LIMIT 300,150

: next 150 lines




I'm assuming you are trying to do pagination, if so you can do it like this:

Let pageSize


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




  • First, enter the id from the last element from your top select rows:

(a) SQL query as follows:

Select top 1 id from (SELECT TOP 150 * FROM (select top 150 * from outlet order by id asc)as d order by id desc) as b order by id asc


  1. Store the selected ID in the Session ViewState object, namely LastOutletID

  2. Declare int PageSize = 150 and also add Session ViewState object

(b) SqlQuery as Follow:

Select the top 150 * from (SELECT TOP 150 * FROM (select the top PageSize * from the socket where id> LastOutletID order by id asc) as d order by id desc) as b order by id asc

  1. Now if you want to select the next top rows, follow step 1 to get the last item ID and save it, then just add 150 to the PageSize, you get the next 151 to 300 rows.


Solution for SQL Server: 1) Get the total number of rows in my table.

For example select count (1) from MYTABLE - 500 rows

2) Use CTE to assign a logical row number to each row in my table.

3) Select the number of lines using the number of lines spacing and the BETWEEN clause.

WITH CTE AS (SELECT ROW_NUMBER () OVER (order from MyColumn1, MyColumn2, MyColumn3) AS Row #, i.e. * FROM MYTABLE t)

select * from CTE, where Row # is from 150 to 300 Order by MyColumn1, MyColumn2, MyColumn3

4) Repeat steps 2) and 3) using the next line interval until the total number of lines is reached.



Since Oracle 12 you have FETCH and OFFSET.

In your example where you want to put 150 lines in one file and the next 150 lines in another file:

Spool file1.txt
select a from b
order by a
Fetch first 150 rows only;
Spool off
Spool file2.txt
select a from b
offset 150 rows
Fetch next 150 rows only;
spool off


This link shows a railroad track diagram of the line limitation proposal from Oracle 12.2 documentation.

Examples here also include "order", which makes a lot of sense if you are looking for a TOP something.

Previous versions of Oracle would have required the use of rownum and order-by, as described in other answers here.



All Articles