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

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

In 12c,

SELECT valFROM   tableORDER BY valOFFSET 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 valFROM   (SELECT val, rownum AS rnum        FROM   (SELECT val                FROM   table                ORDER BY val)        WHERE rownum <= 8)WHERE  rnum > 4;

Here OFFSET is 4.

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

Use the function LAG or LEAD in oracle

The LAG function is used to access data from a previous rowThe 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