SQL (ORACLE): ORDER BY and LIMIT [duplicate]
Prior to 12.1, Oracle does not support the LIMIT or OFFSET keywords. If you want to retrieve rows N through M of a result set, you'd need something like:
SELECT a.* FROM (SELECT b.*, rownum b_rownum FROM (SELECT c.* FROM some_table c ORDER BY some_column) b WHERE rownum <= <<upper limit>>) a WHERE b_rownum >= <<lower limit>>or using analytic functions:
SELECT a.* FROM (SELECT b.*, rank() over (order by some_column) rnk FROM some_table) WHERE rnk BETWEEN <<lower limit>> AND <<upper limit>> ORDER BY some_columnEither of these approaches will sort give you rows N through M of the sorted result.
In 12.1 and later, you can use the OFFSET and/or FETCH [FIRST | NEXT] operators:
SELECT * FROM some_table ORDER BY some_column OFFSET <<lower limit>> ROWS FETCH NEXT <<page size>> ROWS ONLY