SQL (ORACLE): ORDER BY and LIMIT [duplicate]

Question

I want do sorting by property ALL data in my db and ONLY AFTER that use LIMIT and OFFSET.

Query like this:

SELECT select_list     FROM table_expression     [ ORDER BY ... ]     [ LIMIT { number | ALL } ] [ OFFSET number ]  

I know the sorting ends as soon as it has found the first row_count rows of the sorted result. Can I do sorting all data before calling LIMIT and OFFSET?

Answer 1

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_column 

Either 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