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

Question

How to add offset in "select" query in Oracle 11g. I only know how to add a restriction, for example, rownum <= 5 this question is not a duplicate, I already checked other questions and is not related to mine.

So how to add an offset in Oracle 11g?

Answer 1

You can do this easily at 12c by specifying OFFSET .

At 12c ,

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

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

The same request 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

You can use the ROW_NUMBER function for this.

Perhaps 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 3

Use LAG or LEAD function 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 this link for examples.