Возврат части отсортированной выборки

Все нижеследующее верно для Oracle, начиная с версии 8i, когда появились, во-первых, возможность указания сортировки в подзапросе, во-вторых - аналитические функции.

1. Возврат части отсортированной выборки через ROWNUM. Камнем предкновения как правило является то, что значение псевдостолбца ROWNUM определяется до сортировки. Поэтому нижеследуюший запрос не выдаст требуемый результат:

SQL> create table test (value number(3));

Table created.

SQL> insert into test values (1);

1 row created.

SQL> insert into test values (3);

1 row created.

SQL> insert into test values (2);

1 row created.

SQL> select rownum, value from test order by value;

    ROWNUM      VALUE
---------- ----------
         1          1
         3          2
         2          3

Второй момент: указание в запросе выражения WHERE ROWNUM > :N, в отличие от WHERE ROWNUM <= :N, приведет к отсутствию записей в выборке.

SQL> select * from test where rownum > 1;

no rows selected

Поэтому делается следующее:

1. Выдать записи с 10-й по 19-ю из выборки по представлению ALL_TABLES, отсортированной по полю TABLE_NAME:

select o.*
from (select rownum rw
           , o.*
      from (select o.* from all_tables o order by table_name) o
      where rownum < 20
     ) o
where o.rw >= 10;

2. Возрат части отсортированной выборки через аналитическую функцию ROW_NUMBER():

select o.*
from (select o.*
           , row_number() over (order by o.table_name) rw
      from all_tables o
     ) o
where o.rw >= 10
  and o.rw < 20;