danielpradilla danielpradilla - 2 months ago 11
SQL Question

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

Answer

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