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

Lajoie

I would like to copy the first 150 outputs into a file, then the next 150 into another file and so on... – Lajoie

with code? or directly from oracle? – Demit

from oracle, or what do mean with code? – Lajoie

a programming language (not SQL), like C#, etc. – Demit

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

Demit

hm dont get it why you type at the end between 150 and 300. Why 150/300? – Lajoie

it's an example, you can put the numbers you want. – Demit

thats the problem ^^, I think this solution wont work for me. Let say I would like to have the first 150, then the next 150 and so on... and my table got 100000 rows. What do I type for between? :/ – Lajoie

I don't understand if you are calling a stored procedure. If so, you could either pass two range parameters (from / to rownumbers), or just do a loop inside the procedure to get the data in chunks. But still, why is that you need chuncked data? – Demit

if I wanted to copy always 150 rows into a file. So each file got at the end only 150 rows. – Lajoie

I think it's worth mentioning that there should be an order by in the query - otherwise any rownum based selection will be returning a random selection of n values. – Biddle

I personally would have dumped the whole thing and then used a file splitter utiliity (many available online free) FWIW. – Raymund

Answer 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

Shelly

so if I have 100000 rows I have to type a lot of stuff ^^... ? – Lajoie

set up a variable a = 1. LIMIT (a-1)*150,150. Increment a. (This is pseudo SQL) – Shelly

Answer 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)

Pi

pageNumber is the value of all of the rows in the table together right? – Lajoie

@user1258905 - No. Page number is the actual page number. i.e. page 1, page 2 etc. when paging through the records. page = first 150 records, page 2 = next 150 records and so on. – Pi

Answer 4

1. First get id of the last element from your top selection rows by:

(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

2. Save selected id in a Session object of ViewState namely LastOutletID

3. Declare a int PageSize = 150 and also add to Session object of ViewState

(b) SqlQuery as Follow:

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

4. Now if you want to select next top rows, follow step 1 to get last element id and save it then just add 150 to PageSize will give you next 151 to 300 rows

Furan

Answer 5

Solution for SQL Server: 1) Get total row count in my table.

For eg. select count(1) from MYTABLE -- 500 rows

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

3) Select number of rows using row number interval and BETWEEN clause.

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

select * from CTE where Row# between 150 and 300 Order by MyColumn1, MyColumn2, MyColumn3

4) Repeat steps 2) and 3) using next row number interval until row total count is reached.

Edda

Answer 6

Beginning with Oracle 12, you have FETCH and OFFSET.

For your example, where you want to put 150 rows into one file and the next 150 rows into 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 the railroad track diagram of the row limiting clause from the Oracle 12.2 documentation.

The examples here also include "order by" in them, which makes a lot of sense if you are looking for the TOP of something.

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

Africa