Search code examples
sqloracle-databasesql-limit

Alternatives to LIMIT and OFFSET for paging in Oracle

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
Solution

  • 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