Вы можете использовать для этого подзапрос, например

select *
from  
( select * 
  from emp 
  order by sal desc ) 
where ROWNUM <= 5;

Также посмотрите тему О ROWNUM и ограничивающих результатах на Oracle / AskTom для получения дополнительной информации.

Обновлять: Чтобы ограничить результат как нижними, так и верхними границами, все становится немного более раздутым с

select * from 
( select a.*, ROWNUM rnum from 
  ( <your_query_goes_here, with order by> ) a 
  where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum  >= :MIN_ROW_TO_FETCH;

(Скопировано из указанной АскТом-статьи)

Обновление 2: Начиная с Oracle 12c (12.1) доступен синтаксис для ограничения строк или начала смещения.

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

См. Дополнительные примеры в этот ответ. Спасибо Крумии за подсказку.

(непроверено) что-то вроде этого может сработать

WITH
base AS
(
    select *                   -- get the table
    from sometable
    order by name              -- in the desired order
),
twenty AS
(
    select *                   -- get the first 30 rows
    from base
    where rownum < 30
    order by name              -- in the desired order
)
select *                       -- then get rows 21 .. 30
from twenty
where rownum > 20
order by name                  -- in the desired order

Существует также ранг аналитической функции, который можно использовать для сортировки.

Аналитическое решение с одним вложенным запросом:

SELECT * FROM
(
   SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
) 
WHERE MyRow BETWEEN 10 AND 20;

Rank() можно заменить на Row_Number(), но он может вернуть больше записей, чем вы ожидаете, если есть повторяющиеся значения для имени.

Меньше операторов SELECT. Кроме того, меньшее потребление производительности. Кредиты на: [email protected]

SELECT *
    FROM   (SELECT t.*,
                   rownum AS rn
            FROM   shhospede t) a
    WHERE  a.rn >= in_first
    AND    a.rn <= in_first;

Я провел несколько тестов производительности для следующих подходов:

Асктом

select * from (
  select a.*, ROWNUM rnum from (
    <select statement with order by clause>
  ) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW

Аналитический

select * from (
  <select statement with order by clause>
) where myrow between MIN_ROW and MAX_ROW

Короткая альтернатива

select * from (
  select statement, rownum as RN with order by clause
) where a.rn >= MIN_ROW and a.rn <= MAX_ROW

Полученные результаты

В таблице было 10 миллионов записей, сортировка производилась по неиндексированной строке даты и времени:

• План объяснения показал одинаковое значение для всех трех вариантов выбора (323168)
• Но победителем стал AskTom (за которым следуют аналитики).

На выбор первых 10 строк ушло:

• AskTom: 28-30 секунд
• Аналитический: 33-37 секунд
• Короткая альтернатива: 110–140 секунд.

Выбор строк от 100000 до 100 010:

• AskTom: 60 секунд
• Аналитический: 100 секунд

Выбор строк от 9 000 000 до 9 000 010:

• AskTom: 130 секунд
• Аналитический: 150 секунд

В Oracle 12c (см. Пункт об ограничении строк в Справочник по SQL):

SELECT * 
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Запросы пагинации с упорядочением в Oracle действительно сложны.

Oracle предоставляет псевдостолбец ROWNUM, который возвращает число, указывающее порядок, в котором база данных выбирает строку из таблицы или набора объединенных представлений.

ROWNUM - это псевдоколонка, из-за которой многие люди попадают в беду. Значение ROWNUM не присваивается строке постоянно (это распространенное заблуждение). Когда фактически присваивается значение ROWNUM, это может сбивать с толку. Значение ROWNUM присваивается строке после прохождения предикатов фильтра запроса, но перед агрегацией или сортировкой запросов.

Более того, значение ROWNUM увеличивается только после того, как оно присвоено.

Вот почему следующий запрос не возвращает строк:

 select * 
 from (select *
       from some_table
       order by some_column)
 where ROWNUM <= 4 and ROWNUM > 1; 

Первая строка результата запроса не передает предикат ROWNUM> 1, поэтому ROWNUM не увеличивается до 2. По этой причине ни одно значение ROWNUM не становится больше 1, следовательно, запрос не возвращает строк.

Правильно определенный запрос должен выглядеть так:

select *
from (select *, ROWNUM rnum
      from (select *
            from skijump_results
            order by points)
      where ROWNUM <= 4)
where rnum > 1; 

Узнайте больше о запросах пагинации в моих статьях в блоге Vertabelo:

• Объяснение Oracle ROWNUM
• Top-N и запросы пагинации

Начиная с Oracle 12c R1 (12.1), существует является и предложение, ограничивающее строку. В нем не используется знакомый синтаксис LIMIT, но он может работать лучше с большим количеством опций. Вы можете найти полный синтаксис здесь. (Также читайте больше о том, как это работает внутри Oracle в этот ответ).

Чтобы ответить на исходный вопрос, вот запрос:

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

(Для более ранних версий Oracle, пожалуйста, обратитесь к другим ответам в этом вопросе)

Примеры:

Следующие примеры были процитированы из связанная страница в надежде предотвратить гниение ссылок.

Настраивать

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

Что в таблице?

SELECT val
FROM   rownum_order_test
ORDER BY val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

Получить первые строки N

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

Получить первые строки N, если строка N^th имеет связи, получить все связанные строки

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.

Верхний CDD: 333% строк

SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

Использование смещения, очень полезно для разбивки на страницы

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

Вы можете комбинировать смещение с процентами

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

Я начал подготовку к экзамену Oracle 1z0-047, подтвержденному на 12c Во время подготовки к нему я наткнулся на усовершенствование 12c, известное как FETCH FIRST. Это позволяет вам получать строки / ограничивать строки по вашему усмотрению. С ним доступны несколько вариантов

- FETCH FIRST n ROWS ONLY
 - OFFSET n ROWS FETCH NEXT N1 ROWS ONLY // leave the n rows and display next N1 rows
 - n % rows via FETCH FIRST N PERCENT ROWS ONLY

Пример:

Select * from XYZ a
order by a.pqr
FETCH FIRST 10 ROWS ONLY

Если вы не используете Oracle 12C, вы можете использовать запрос TOP N, как показано ниже.

SELECT *
 FROM
   ( SELECT rownum rnum
          , a.*
       FROM sometable a 
   ORDER BY name
   )
WHERE rnum BETWEEN 10 AND 20;

Вы даже можете переместить это предложение в предложение with следующим образом

WITH b AS
( SELECT rownum rnum
      , a.* 
   FROM sometable a ORDER BY name
) 
SELECT * FROM b 
WHERE rnum BETWEEN 10 AND 20;

На самом деле здесь мы создаем встроенное представление и переименовываем rownum в rnum. Вы можете использовать rnum в основном запросе в качестве критерия фильтрации.

select * FROM (SELECT 
   ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
 FROM EMP ) EMP  where ROWID=5

больше значений узнать

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID>5

меньше значений узнают

select * FROM (SELECT 
       ROW_NUMBER() OVER (ORDER BY sal desc),* AS ROWID, 
     FROM EMP ) EMP  where ROWID=5

Для каждой строки, возвращаемой запросом, псевдостолбец ROWNUM возвращает число, указывающее порядок, в котором Oracle выбирает строку из таблицы или набора соединенных строк. Первая выбранная строка имеет ROWNUM 1, вторая - 2 и так далее.

  SELECT * FROM sometable1 so
    WHERE so.id IN (
    SELECT so2.id from sometable2 so2
    WHERE ROWNUM <=5
    )
    AND ORDER BY so.somefield AND ROWNUM <= 100 

Я реализовал это на сервере oracle11.2.0.1.0

Стандарт SQL

Начиная с версии 12c Oracle поддерживает стандарт SQL: 2008 Standard, который предоставляет следующий синтаксис для ограничения набора результатов SQL:

SELECT
    title
FROM
    post
ORDER BY
    id DESC
FETCH FIRST 50 ROWS ONLY

Oracle 11g и более ранние версии

До версии 12c для получения записей Top-N нужно было использовать производную таблицу и псевдостолбец ROWNUM:

SELECT *
FROM (
    SELECT
        title
    FROM
        post
    ORDER BY
        id DESC
)
WHERE ROWNUM <= 50

Как расширение принятый ответ Oracle внутренне использует функции ROW_NUMBER/RANK. Синтаксис OFFSET FETCH - это синтаксический сахар.

Это можно было наблюдать с помощью процедуры DBMS_UTILITY.EXPAND_SQL_TEXT:

Подготовка образца:

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;
COMMIT;

Запрос:

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

регулярно:

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
               ROW_NUMBER() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rownumber" 
      FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$$_rownumber"<=5 ORDER BY "A1"."rowlimit_$_0" DESC;

db <> демо скрипта

Получение развернутого текста SQL:

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS ONLY',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

WITH TIES расширяется как RANK:

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
          FROM   rownum_order_test
          ORDER BY val DESC
          FETCH FIRST 5 ROWS WITH TIES',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/

SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
              RANK() OVER ( ORDER BY "A2"."VAL" DESC ) "rowlimit_$$_rank" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
WHERE "A1"."rowlimit_$$_rank"<=5 ORDER BY "A1"."rowlimit_$_0" DESC

и смещение:

declare
  x VARCHAR2(1000);
begin
 dbms_utility.expand_sql_text(
        input_sql_text => '
          SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY',
        output_sql_text => x);

  dbms_output.put_line(x);
end;
/


SELECT "A1"."VAL" "VAL" 
FROM  (SELECT "A2"."VAL" "VAL","A2"."VAL" "rowlimit_$_0",
             ROW_NUMBER() OVER ( ORDER BY "A2"."VAL") "rowlimit_$$_rownumber" 
       FROM "ROWNUM_ORDER_TEST" "A2") "A1" 
       WHERE "A1"."rowlimit_$$_rownumber"<=CASE  WHEN (4>=0) THEN FLOOR(TO_NUMBER(4)) 
             ELSE 0 END +4 AND "A1"."rowlimit_$$_rownumber">4 
ORDER BY "A1"."rowlimit_$_0"

В случае SQL-Developer он автоматически выбирает только первые 50 строк. И если мы прокрутим вниз, получится еще 50 строк и так далее!

Следовательно, в случае инструмента sql-developer нам не нужно определять!