Clever Geek Handbook

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

+3


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

.

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

+8


source


LIMIT 150

or LIMIT 0,150

: first 150 lines

LIMIT 150,150

: next 150 lines



LIMIT 300,150

: next 150 lines

etc.

+7


source


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

Let pageSize

be150 

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


source


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


source


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.

0


source


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.

0


source






More articles:


All Articles