How to add offset in a "select" query in Oracle 11g?

Question

How to add an offset in a "select" query in Oracle 11g. I only know how to add the limit by e.g rownum <= 5 this question is not a duplicate, I already checked the other questions and are not related to mine.

So, how to add the offset in Oracle 11g ?

Answer 1

You can do it easily on 12c by specifying OFFSET.

In 12c,

SELECT val
FROM   table
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

To do the same on 11g and prior, you need to use ROWNUM twice, inner query and outer query respectively.

The same query in 11g,

SELECT val
FROM   (SELECT val, rownum AS rnum
        FROM   (SELECT val
                FROM   table
                ORDER BY val)
        WHERE rownum <= 8)
WHERE  rnum > 4;

Here OFFSET is 4.

Answer 2

Use the function LAG or LEAD in oracle

The LAG function is used to access data from a previous row

The LEAD function is used to return data from the next row

Usage:-

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

Please find the this link for examples

Answer 3

You can use ROW_NUMBER function for that.

Maybe this helps:

SELECT *
  FROM(SELECT t.*,
              ROW_NUMBER() OVER (ORDER BY ...) rn -- whatever ordering you want
         FROM your_table t
      )
 WHERE rn >= ... -- your offset

Hope that helps

Answer 4

for oracle 11g, suppose you have a table user, for limit and offset you can do:

    SELECT * FROM (
         SELECT USER.*,
                row_number() over (partition by 1 order by 1) as rnum
         from USER
     )
where rnum <= 100 --limit
  and rnum > 0 --offset

let me know if it works for you

Answer 5

For Oracle 11g

Based on @Ibrahima Timera's answer, but with a behavior like Postgres's LIMIT and OFFSET, with:

• LIMIT = max number of rows fetched
• OFFSET = skipped rows before first fetched element

In the example below on a USER table, 10 is the LIMIT, 50 is the OFFSET :

SELECT * FROM (
         SELECT USER.*,
                row_number() over (partition by 1 order by 1) as rnum
         from USER
     )
where rnum <= (10 + 50) -- LIMIT + OFFSET
and rnum > 50 -- OFFSET

Answer 6

For pagination in Oracle 11g , We can use ROWNUM.

This is the example.

WITH STUDENT_PAGINATION AS (
    SELECT
        STUDENT.*,
        ROWNUM AS RN
    FROM
        STUDENT
    ORDER BY
        ID
)
SELECT
    *
FROM
    STUDENT_PAGINATION
WHERE
    RN >=5
    AND RN <=10;