Alternatives to LIMIT and OFFSET for paging in Oracle

Question

I'm developing a web application and need to page ordered results. I normaly use LIMIT/OFFSET for this purpose.

Which is the best way to page ordered results in Oracle? I've seen some samples using rownum and subqueries. Is that the way? Could you give me a sample for translating this SQL to Oracle:

SELECT fieldA,fieldB 
FROM table 
ORDER BY fieldA 
OFFSET 5 LIMIT 14

(I'm using Oracle 10g, for what it's worth)

Thanks!

Answer: Using the link provided below by karim79, this SQL would look like:

SELECT * FROM (
    SELECT rownum rnum, a.* 
    FROM(
        SELECT fieldA,fieldB 
        FROM table 
        ORDER BY fieldA 
    ) a 
    WHERE rownum <=5+14
)
WHERE rnum >=5

I think the last line should rather say `WHERE rnum >5` (not `>=`), otherwise you'll get 15 records, skipping only the first four. — peterp, Jul 11 '13 at 09:19

score 28 · Answer 1 · answered Sep 01 '14 at 06:33

28

As of oracle 12c, you could use the top N queries.

SELECT fieldA,fieldB 
FROM table 
ORDER BY fieldA 
OFFSET 5 ROWS FETCH NEXT 14 ROWS ONLY;

http://www.oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php

answered Sep 01 '14 at 06:33

Montri M

1,496
11
12

Concerning `OFFSET`, please have a look at Markus Winand [Paging Through Results](http://use-the-index-luke.com/sql/partial-results/fetch-next-page). – Ludovic Kuty Oct 31 '16 at 19:20
Could you please guide here : https://stackoverflow.com/questions/62499069/apply-offset-and-limit-in-oracle-for-complex-join-queries? – Pra_A Jun 21 '20 at 13:23

score 22 · Answer 2 · answered Mar 07 '11 at 15:30

22

Since you're on 10g, you should be able to simplify the ROWNUM approach using analytic functions

SELECT fieldA, 
       fieldB
  FROM (SELECT fieldA,
               fieldB,
               row_number() over (order by fieldA) rnk
          FROM table_name)
 WHERE rnk BETWEEN 5 AND 14;

answered Mar 07 '11 at 15:30

Justin Cave

212,688
21
342
361

3

thanks a lot, this is exactly what i needed :) why oh why does oracle insist on doing things the hard way! – mic Apr 08 '14 at 13:09

score 18 · Accepted Answer · edited Nov 06 '12 at 10:07

18

You will need to use the rownum pseudocolumn to limit results. See here:

~~http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html~~

http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

edited Nov 06 '12 at 10:07

sleske

73,934
32
166
212

answered May 26 '10 at 10:47

karim79

326,960
63
404
402

interesting read on how rownum operates : http://blog.lishman.com/2008/03/rownum.html – xastor Oct 07 '11 at 13:03

score 8 · Answer 4 · answered Apr 23 '19 at 12:29

Method-1: For database version Oracle12c or higher

SELECT fieldA, fieldB 
FROM table 
ORDER BY fieldA 
OFFSET 5 ROWS FETCH NEXT 14 ROWS ONLY

Method-2: For database version Oracle11g or lower using analytical function RowNumber()

SELECT fieldA, fieldB
FROM (
    SELECT fieldA, fieldB,
        row_number() over (order by fieldA) rowRank
    FROM table_name
)
WHERE rowRank BETWEEN 5 AND 14;

Method-3: For database version Oracle11g or lower using RowNum

SELECT T.* FROM ( 
SELECT T.*, rowNum as rowIndex
FROM (
    SELECT fieldA, fieldB,
    FROM table_name
)T)T
WHERE rowIndex > 0 AND rowIndex <= 20;

In some cases, I have found method-3 is faster than method-2 since order by clause is mandatory in method 2. However, if your database version is 12c or higher you must go for method-1.

Could you please guide here: https://stackoverflow.com/questions/62499069/apply-offset-and-limit-in-oracle-for-complex-join-queries ? — Pra_A, Jun 21 '20 at 13:22

Alternatives to LIMIT and OFFSET for paging in Oracle

4 Answers4

Linked

Related