SQL TOP, FETCH FIRST и WITH TIES

Урок 2.9 · Время чтения: ~9 мин · Задачи для практики ↓

SQL TOP и FETCH FIRST n ROWS ONLY - альтернативы LIMIT: TOP используется в SQL Server, а FETCH FIRST - это стандарт SQL, который поддерживают PostgreSQL, Oracle, MariaDB и другие СУБД. В этом уроке вы узнаете стандартный синтаксис, постраничный вывод с OFFSET ... FETCH, опцию WITH TIES, которая сохраняет строки с равными значениями, PERCENT, а также старые способы Oracle и Firebird.

В уроке 2.7 вы ограничивали результат с помощью LIMIT и OFFSET. Этот синтаксис работает в MySQL, MariaDB, PostgreSQL и SQLite, но SQL Server и Oracle его не принимают. Когда вы переходите на другую СУБД, читаете чужие запросы или готовитесь к собеседованию, вы встретите ещё несколько способов записать ту же идею.

Почему у ограничения строк несколько синтаксисов

Долгое время ограничения строк не было в стандарте SQL, и каждая СУБД придумала свой синтаксис:

• MySQL и PostgreSQL добавили LIMIT, SQLite его позаимствовал.
• SQL Server добавил TOP.
• В Oracle был псевдостолбец ROWNUM.
• Firebird добавил FIRST и SKIP.

Стандартное предложение FETCH FIRST появилось только в SQL:2008, когда эти привычки уже сложились. Сегодня большинство СУБД поддерживают стандартную форму вместе со своей, но MySQL и SQLite по-прежнему нет.

FETCH FIRST n ROWS ONLY: стандартный синтаксис

FETCH FIRST пишется после ORDER BY, на месте LIMIT:

SELECT title, length
FROM film
ORDER BY length
FETCH FIRST 3 ROWS ONLY;

Результат: три самых коротких фильма длиной 46 минут. Запрос работает в MariaDB 10.6+, PostgreSQL и Oracle; MySQL выдаёт синтаксическую ошибку.

Слова FIRST и NEXT, ROW и ROWS взаимозаменяемы: FETCH NEXT 1 ROW ONLY означает то же, что FETCH FIRST 1 ROWS ONLY. Выбирайте то, что лучше читается. Если число не указано, FETCH FIRST ROW ONLY возвращает одну строку.

Постраничный вывод с OFFSET ... FETCH NEXT

Стандартная форма OFFSET пишется со словом ROWS и стоит перед FETCH:

SELECT title, length
FROM film
ORDER BY length, title
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

Результат: фильмы с 11-го по 15-й по длине, как и LIMIT 5 OFFSET 10.

В SQL Server это единственный способ постраничного вывода, и там он требует ORDER BY: OFFSET без сортировки - синтаксическая ошибка.

WITH TIES: строки с тем же значением, что у последней

Снова посмотрим на самые короткие фильмы. Длина 46 минут у пяти фильмов, но FETCH FIRST 3 ROWS ONLY возвращает только три из них, и какие именно - решает СУБД.

WITH TIES решает эту проблему: возвращает первые N строк и все следующие строки с такими же значениями сортировки, как у строки N.

SELECT title, length
FROM film
ORDER BY length
FETCH FIRST 3 ROWS WITH TIES;

Результат: 5 строк. Возвращаются все пять фильмов по 46 минут, потому что у третьего фильма такая же длина, как ещё у двух.

WITH TIES честно отвечает на вопросы вроде «три самых коротких фильма»: ни один фильм не теряется только из-за того, что его значение совпало с последним.

Правила WITH TIES

• Нужен ORDER BY: равенство определяется по столбцам сортировки. Без сортировки PostgreSQL и MariaDB выдают ошибку.
• Дополнительный столбец сортировки убирает равенства. При ORDER BY length, title одинаковых строк нет, и WITH TIES возвращает ровно 3 строки.
• Результат может быть намного больше N. Самые длинные фильмы Sakila идут 185 минут, и ORDER BY length DESC FETCH FIRST 3 ROWS WITH TIES возвращает 10 фильмов.

Тот же запрос в PostgreSQL на базе Bookings находит самые длинные маршруты:

SELECT flight_no, departure_city, arrival_city, duration
FROM routes
ORDER BY duration DESC
FETCH FIRST 3 ROWS WITH TIES;

Результат: 4 маршрута по 8 часов 50 минут - рейсы между Москвой и Южно-Сахалинском или Петропавловском.

WITH TIES в FETCH поддерживают PostgreSQL 13+, MariaDB 10.6+ и Oracle 12c+.

SQL Server: TOP

В SQL Server TOP пишется сразу после SELECT:

SELECT TOP (3) Name, ListPrice
FROM Product
ORDER BY ListPrice DESC;

Результат: три самых дорогих товара в AdventureWorks.

Для числа скобки необязательны (TOP 3 тоже работает), но рекомендуются: со скобками TOP принимает и переменную или выражение, например TOP (@n).

У TOP есть свой WITH TIES, и работает он только с TOP: OFFSET ... FETCH в SQL Server WITH TIES не поддерживает.

SELECT TOP (3) WITH TIES Name, ListPrice
FROM Product
ORDER BY ListPrice DESC;

Результат: 5 товаров. Пять размеров велосипеда Road-150 Red стоят одинаково - 3578.27.

Подводные камни TOP

• TOP без ORDER BY допустим, но возвращает произвольные строки, как и LIMIT без сортировки.
• TOP ... WITH TIES без ORDER BY - ошибка.
• SQL Server не принимает FETCH FIRST сам по себе: FETCH разрешён только после OFFSET. Для первых строк пишите OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY или используйте TOP.

Как вернуть процент строк

Иногда нужно не 10 строк, а 10% строк. SQL Server и Oracle умеют это напрямую:

SELECT TOP (1) PERCENT Name, ListPrice
FROM Product
ORDER BY ListPrice DESC;

Результат: 3 строки. 1% от 295 товаров - это 2,95, и SQL Server округляет вверх.

В Oracle используется стандартная форма:

SELECT first_name, last_name, salary
FROM hr.employees
ORDER BY salary DESC
FETCH FIRST 5 PERCENT ROWS ONLY;

Результат: 6 сотрудников с самыми высокими зарплатами: 5% от 107 - это 5,35, округление вверх.

DuckDB записывает процент в LIMIT: LIMIT 10%. PostgreSQL, MariaDB и MySQL проценты не поддерживают: там сначала считают строки и вычисляют число самостоятельно.

Oracle: ROWNUM и FETCH FIRST

До версии 12c в Oracle не было FETCH FIRST, и старый код ограничивает строки псевдостолбцом ROWNUM. Он нумерует строки по мере чтения, до того как применяется ORDER BY. Поэтому такой запрос неверен:

SELECT first_name, last_name, salary
FROM hr.employees
WHERE ROWNUM <= 3
ORDER BY salary;

Результат: не три самые низкие зарплаты, а первые три прочитанные строки (17000, 17000 и 24000), отсортированные уже после отбора.

Чтобы сначала отсортировать, старый код оборачивает отсортированный запрос в подзапрос и применяет ROWNUM к его результату:

SELECT first_name, last_name, salary
FROM (
    SELECT first_name, last_name, salary
    FROM hr.employees
    ORDER BY salary
)
WHERE ROWNUM <= 3;

Результат: три самые низкие зарплаты: 2100, 2200 и 2200.

Ещё одна ловушка: WHERE ROWNUM > 1 не возвращает ни одной строки. Первая строка получает номер 1, не проходит условие и отбрасывается; следующая строка снова получает номер 1, и так далее.

В Oracle 12c и новее просто используйте FETCH FIRST, в том числе с WITH TIES:

SELECT first_name, last_name, salary
FROM hr.employees
ORDER BY salary DESC
FETCH FIRST 7 ROWS WITH TIES;

Результат: 8 сотрудников: у 7-го и 8-го одинаковая зарплата 12008.

LIMIT Oracle не принимает до сих пор, даже в версии 26ai.

Firebird: FIRST, SKIP и ROWS

У Firebird два собственных синтаксиса. FIRST и SKIP пишутся сразу после SELECT, как TOP:

SELECT FIRST 3 SKIP 2 first_name, last_name, salary
FROM employee
ORDER BY salary DESC;

Результат: сотрудники с 3-го по 5-й по зарплате: две строки пропущены, три возвращены.

ROWS m TO n пишется в конце запроса и принимает номера строк, а не их количество:

SELECT first_name, last_name, salary
FROM employee
ORDER BY salary DESC
ROWS 3 TO 5;

Результат: те же сотрудники с 3-го по 5-й. ROWS 3 без TO означает первые три строки.

С Firebird 3.0 работает и стандартный OFFSET ... FETCH. WITH TIES не поддерживается даже в Firebird 5.

WITH TIES в MySQL и SQLite

В MySQL и SQLite нет ни FETCH, ни WITH TIES, но тот же результат можно собрать из инструментов, которые вы изучите позже в курсе. Вот как это выглядит.

Подзапрос находит значение третьей строки, а внешний запрос берёт все фильмы, которые не длиннее его:

SELECT title, length
FROM film
WHERE length <= (
    SELECT length
    FROM film
    ORDER BY length
    LIMIT 1 OFFSET 2
)
ORDER BY length, title;

Результат: те же 5 фильмов по 46 минут, что и у FETCH FIRST 3 ROWS WITH TIES. Подзапросы разбираются в уроке 6.2.

Оконная функция RANK() даёт равным значениям одинаковый ранг, поэтому условие rnk <= 3 сохраняет все равенства:

SELECT title, length
FROM (
    SELECT title, length, RANK() OVER (ORDER BY length) AS rnk
    FROM film
) AS ranked
WHERE rnk <= 3
ORDER BY length, title;

Результат: те же 5 фильмов. RANK() разбирается в уроке 7.2.

Синтаксис ограничения строк в разных СУБД

Если запрос должен работать в нескольких СУБД, стандартный OFFSET ... FETCH с ORDER BY покрывает больше всего из них: все, кроме MySQL и SQLite. Этим уроком заканчиваются основы выборки строк; в следующем модуле вы узнаете, как преобразовывать значения с помощью функций SQL.

Ключевые выводы:

• FETCH FIRST n ROWS ONLY - стандартная альтернатива LIMIT; MySQL и SQLite её не поддерживают.
• OFFSET m ROWS FETCH NEXT n ROWS ONLY выводит результат по страницам; в SQL Server для него нужен ORDER BY.
• WITH TIES добавляет строки с теми же значениями сортировки, что у последней строки, поэтому результат может быть больше N.
• В SQL Server используются TOP (n), TOP (n) WITH TIES и TOP (n) PERCENT.
• ROWNUM в Oracle присваивается до ORDER BY: сортируйте в подзапросе или используйте FETCH FIRST в 12c+.
• В MySQL и SQLite WITH TIES строится подзапросом или через RANK().

Часто задаваемые вопросы

Чем заменить LIMIT в SQL Server?

Для первых строк используйте SELECT TOP (n) ..., для постраничного вывода - ORDER BY ... OFFSET m ROWS FETCH NEXT n ROWS ONLY. SQL Server не принимает LIMIT и не разрешает FETCH без OFFSET.

Что делает WITH TIES в SQL?

Возвращает первые N строк и все следующие строки с такими же значениями в столбцах ORDER BY, как у строки N. FETCH FIRST 3 ROWS WITH TIES может вернуть 3, 5 или 10 строк - в зависимости от того, у скольких строк третье значение.

Поддерживает ли MySQL FETCH FIRST или WITH TIES?

Нет. MySQL 8 и 9 поддерживают только LIMIT. MariaDB поддерживает FETCH FIRST ... WITH TIES с версии 10.6. В MySQL тот же результат получают подзапросом или с помощью RANK().

Как ограничить число строк в Oracle?

В Oracle 12c и новее используйте FETCH FIRST n ROWS ONLY после ORDER BY. В старых версиях сортируйте в подзапросе и фильтруйте его результат условием WHERE ROWNUM <= n. LIMIT Oracle не поддерживает.

Чем FETCH FIRST отличается от FETCH NEXT?

Ничем: FIRST и NEXT - синонимы, как и ROW и ROWS. По соглашению FETCH FIRST пишут без OFFSET, а FETCH NEXT - после него.