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 ?

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

Solution 2

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

Comments

[Lalit Kumar B]

All you need is ROWNUM

[Lalit Kumar B]

@psaraj12, if you mean to get rows in descending order, then just use DESC in inner query.

[Toolkit]

how to do this with LEFT OUTER JOIN?

[Lalit Kumar B]

@Toolkit Doesn't matter. You could have your result set as inner query and apply the TOP N query.

[Toolkit]

@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

[Lalit Kumar B]

@Toolkit Apply on the result set. Or use a WITH clause to make it look simpler

[Witold Kaczurba]

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