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 ?

The question should be reworded as your meaning of offset may or may not be related to the OFFSET clause. — Witold Kaczurba, Jun 22 '17 at 05:24
Possible duplicate of [Paging with Oracle](https://stackoverflow.com/questions/241622/paging-with-oracle) — Vadzim, Apr 24 '18 at 16:16

score 25 · Accepted Answer · edited Nov 24 '14 at 07:31

25

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.

edited Nov 24 '14 at 07:31

Jeffrey Kemp

59,135
14
106
158

answered Nov 24 '14 at 07:07

Lalit Kumar B

47,486
13
97
124

1

@psaraj12, if you mean to get rows in descending order, then just use `DESC` in `inner query`. – Lalit Kumar B Nov 24 '14 at 15:19
how to do this with LEFT OUTER JOIN? – Toolkit Feb 05 '17 at 13:02
@Toolkit Doesn't matter. You could have your result set as inner query and apply the TOP N query. – Lalit Kumar B Feb 05 '17 at 16:55
@LalitKumarB thanks, I am now trying to do this with a materialized view that is a JOIN query. Any idea how to do pagination based on parent table? thanks – Toolkit Feb 06 '17 at 10:45
@Toolkit Apply on the result set. Or use a WITH clause to make it look simpler – Lalit Kumar B Feb 06 '17 at 13:24

score 3 · Answer 2 · answered Nov 24 '14 at 07:09

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

score 1 · Answer 3 · answered Dec 20 '21 at 18:12

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

score 0 · Answer 4 · answered Nov 24 '14 at 07:04

0

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

answered Nov 24 '14 at 07:04

psaraj12

4,772
2
21
30

All you need is `ROWNUM` – Lalit Kumar B Nov 24 '14 at 07:09
@Lalit Kumar B. what you wrote makes sense as long as limiting rows in the ouptut is concerned. I would say that the question is not fully clear and should be reworded (offset may be understood differently). – Witold Kaczurba Jun 22 '17 at 05:27

score 0 · Answer 5 · answered Mar 21 '23 at 10:37

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;

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

5 Answers5

Linked