Выбор последней записи в Oracle: ORDER BY и ROWNUM

Быстрый ответ

Для того, чтобы верно использовать ORDER BY и ROWNUM в Oracle, следует прибегнуть к подзапросам. Сначала произведите сортировку результатов внутри подзапроса. Только после этого примените ограничение ROWNUM:

SELECT * FROM ( 
  -- Сначала сортируем результаты
  SELECT col1, col2, ROWNUM AS rn
  FROM your_table
  ORDER BY col1, col2 DESC  
) 
WHERE rn <= 10;  -- Теперь применяем ROWNUM

Такой подход позволяет извлечь первые 10 записей из уже отсортированных данных.

Особенности использования: ORDER BY, ROWNUM и WHERE-клауза

Отметим, что в SQL Oracle важен порядок выполнения операторов, особенно при работе с ORDER BY и ROWNUM. Клауза WHERE исполняется раньше ORDER BY, поэтому прямое применение ROWNUM может приносить неожиданные результаты.

Порядок выполняемых операций

Для корректного сочетания ROWNUM и ORDER BY важно уяснить, что ROWNUM присваивается перед сортировкой строк. Именно поэтому подзапросы являются не просто хорошей практикой, а обязательным условием. В противном случае прийдется столкнуться с абсолютно неожиданными результатами.

-- Этот запрос не даст ожидаемого результата, не повторяйте его!
SELECT ROWNUM, col1
FROM your_table
WHERE ROWNUM <= 10
ORDER BY col1;

В данном случае ROWNUM присваивается до сортировки, и ORDER BY на результат не влияет, что приводит к непредсказуемым и неотсортированным данным.

Подробнее об этом расскажет наш спикер на видео

Альтернатива для ROWNUM

Если работаете на Oracle 12c или более поздних версиях, рекомендуем воспользоваться клаузами FETCH FIRST и OFFSET. Они делают код более понятным:

SELECT col1, col2
FROM your_table
ORDER BY col1 DESC  -- Сортируем данные в обратном порядке.
FETCH FIRST 10 ROWS ONLY;  -- Современный синтаксис для выборки первых 10 строк.

Учет временных меток

Иной раз можно обойтись без сортировки при выборе самых свежих записей, если использовать функции агрегации, например, MAX(), в подзапросе:

SELECT t.*
FROM (
  SELECT MAX(t_stamp), col1
  FROM your_table
  GROUP BY col1
) subq
JOIN your_table t ON t.t_stamp = subq.MAX(t_stamp);

Оптимизация выборки данных

Важно экономично подходить к выборке данных, подобно тому как не стоит переедать на буфете. По этой причине рекомендуется выбирать конкретные столбцы, а не использовать SELECT * без необходимости:

SELECT col1, col2, ROWNUM AS rn
FROM your_table 
ORDER BY col1, col2 DESC

Визуализация

Можно представить принципы работы ORDER BY и ROWNUM в Oracle с помощью аналогии с колодой карт.

Карты до сортировки: [🂡, 🂷, 🂮, 🂢, 🃑]

Нам надо их отсортировать (ORDER BY) и взять первые три (ROWNUM <= 3).

Шаг 1: Сортируем колоду    ---->    [🂢, 🂡, 🂷, 🂮, 🃑]
Шаг 2: Берем первые три     ---->    [🂢, 🂡, 🂷]

После сортировки мы забираем карты, которые решили использовать – это можно сравнить с подготовкой колоды карт к игре.

Экономия времени: использование TO_DATE и ручных ограничений

Знакомство с TO_DATE

Функция TO_DATE в Oracle позволяет эффективно ограничивать по времени результаты:

SELECT col1
FROM (
  SELECT col1, col2, ROWNUM AS rn
  FROM your_table
  WHERE col2 >= TO_DATE('2023-01-01', 'YYYY-MM-DD')  -- Да придет с нами новый 2023 год!
  ORDER BY col2
)
WHERE rn <= 10;

Внесение ручных ограничений данных

Чтобы выбрать данные за определенный период, наиболее эффективно будет использовать клаузу «WHERE» внутри подзапроса:

SELECT col1, col2
FROM (
  SELECT col1, col2
  FROM your_table
  ORDER BY col2 DESC  -- Делаем путешествие в прошлое!
)
WHERE col2 BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-01-31', 'YYYY-MM-DD');

Акцентируйте внимание на эффективности запросов и правильно поставьте ограничения в подзапросах, которые соответствуют вашим целям.

Полезные материалы

1. Документация Oracle о ORDER BY и ROWNUM
2. Ask TOM: О ROWNUM и ограничении выборки
3. ROWNUM против ROW_NUMBER() – Ask TOM на Oracle
4. Stack Overflow: Как использовать ROWUM в Oracle с максимальной эффективностью
5. Оптимизация в Oracle: применение ROWNUM – C# Corner