SQL оператор SELECT TOP
В этом учебном материале вы узнаете, как использовать оператор SELECT TOP в SQL, с синтаксисом и примерами.
Описание
SQL оператор SELECT TOP используется для извлечения записей из одной или нескольких таблиц в базе данных и ограничения количества возвращаемых записей на основе фиксированного значения или процента.
Подсказка: SELECT TOP является частной версией Microsoft для ограничения ваших результатов и может использоваться в базах данных, таких как SQL Server и MSAccess.
Для других баз данных SQL попробуйте оператор SELECT LIMIT.
Синтаксис
Синтаксис для оператора SELECT TOP в SQL.
Параметры или аргумент
Пример — использование ключевого слова TOP
Давайте посмотрим на пример SQL, где мы используем ключевое слово TOP в операторе SELECT.
Например.
SQL — Select first 10 rows only?
How do I select only the first 10 results of a query?
I would like to display only the first 10 results from the following query:
12 Answers 12
In SQL server, use:
Depends on your RDBMS
The ANSI SQL answer is FETCH FIRST .
If you want ties to be included, do FETCH FIRST 10 ROWS WITH TIES instead.
To skip a specified number of rows, use OFFSET , e.g.
Will skip the first 20 rows, and then fetch 10 rows.
Supported by newer versions of Oracle, PostgreSQL, MS SQL Server, Mimer SQL and DB2 etc.
MS SQL 2011 – новый оператор Offset
Использование данной команды позволяет пропустить указанное количество строк перед тем как выводить результаты запроса. Что под этим подразумевается: Допустим, у нас есть 100 записей в таблице и нужно пропустить первые 10 строк и вывести строки с 11 по 100. Теперь это легко решается следующим запросом:
Для тех товарищей, которые практикуют .Net должен быть знаком метод расширения для коллекций Skip, который пропускает указанное количество строк. Так вот выражение Offset работает точно так же. После того как данные упорядочены каким-либо образом, можно применять выражение Offset.
Ситуации, в которых может быть использовано выражение Offset
Во всех последующих примерах на Offset будет использовать набор данных построенных в результате данного скрипта:
Задача 1. Пропустить первые 10 записей и показать остальные.
Скрипт будет простой.
Вывод результатов будет таким:
Неважно, какое слово использовать после указания количества строк: Row или Rows – они синонимы в данном случае.
Задача 2. Передать количество строк для пропуска в виде переменной
Задача 3. Задать количество строк для пропуска в виде выражения
Выражение select MAX(number)/99999999 from master..spt_values вернет число 14.
Задача 4. Задать количество строк для пропуска в виде пользовательской функции
Код для скалярной пользовательской функции
Задача 5. Использование Offset с Order by внутри представлений (view), функций, подзапросах, вложенных таблицах, общих выражениях для таблиц (Common Table Expressions — CTE).
Например, использование в общих выражениях.
Пример ниже показывает использование Offset и Order by внутри вложенной таблицы.
И еще пример на работу Offset и Order с представлениями.
Когда Offset не будет работать
1. Так как это «метод расширения», то без выражения order by ничего работать не будет.
Сообщит об ошибке
Msg 102, Level 15, State 1, Line 21 Incorrect syntax near ’10’.
2. Нельзя задавать отрицательное значение для Offset.
Движок SQL сервера выдаст
Msg 10742, Level 15, State 1, Line 22 The offset specified in a OFFSET clause may not be negative.
3. Нельзя задавать значения отличные от целочисленного типа.
Msg 10743, Level 15, State 1, Line 24 The number of rows provided for a OFFSET clause must be an integer.
4. Не может быть использован внутри выражения Over().
Во время выполнения запроса получим сообщение
Msg 102, Level 15, State 1, Line 22 Incorrect syntax near ‘Offset’.
Использование Fetch First / Fetch Next
Эти ключевые слова используются для уточнения количества возвращаемых строк после пропуска массива строк по выражению Offset. Представьте, что у нас есть 100 строк и нам надо пропустить первые 10 и получить следующие 5 строк. Т.е. надо получить строки с 11 по 15.
Такой запрос вернет ожидаемое кол-во строк. Программисты на .Net тут же припомнят метод расширения Take.
Далее рассмотрим ситуации, где можно применить эти ключевые слова.
Задача 1. Пропустить первые 10 записей и получить следующие 5
Результат будет таким:
Задача 2. Задать количество строк для вывода с помощью переменной
В целом и общем, с этими ключевыми словами можно делать все то же самое, что и с Offset. Подзапросы, представления, функции и т.д.
Когда Fetch First / Fetch Next не будут работать
Ограничения на эти ключевые слова полностью совпадают с ограничениями на Offset.
Симуляция Offset и Fetch Next для Sql Server 2005/2008
В предыдущих версиях SQL сервера можно было получить тот же функционал путем применения функции ранжирования Row_Number(). Конечно код получался не такой изящный и лаконичный, например:
Внутри CTE идет генерация служебной колонки которая просто нумерует строки, после чего строки фильтруются по этому полю. Способ не самый быстрый как вы понимаете.
Симуляция Offset и Fetch Next для Sql Server 2000
Для этих древних серверов не было функций ранжирования, но и тогда можно было повторить обсуждаемый функционал. Тогда в ход шли временные таблицы с авто инкрементальным полем. Пример скрипта:
В этом скрипте сначала создается временная таблица, куда перезаписываются данные из целевой таблицы. Причем во временной таблице есть автоинкрементальное поле, по которому потом и осуществляется запрос нужных строк.
Практическое применение Offset и Fetch с замерами времени и ресурсов
Я уверен, что всё предыдущее объяснение об использовании и назначении Offset и Fetch подвело вас к ясному пониманию, зачем они нужны и где их можно использовать. Родились идеи по оптимизации существующего кода. Далее мы рассмотрим пример из реальной практики, когда может пригодиться Offset. Так же будут приведены результаты замеров производительности на разных SQL серверах. Тесты будут прогоняться на выборке из 1 миллиона строк.
Для начала создадим счет-таблицу по следующему скрипту.
Постраничный просмотр данных на стороне сервера
Постраничный просмотр является наиболее часто встречающейся функцией в системах просмотра записей из каких-либо баз. Теперь это возможно проделывать как на стороне клиента, так и на стороне сервера. Пэйджинг на стороне клиента подразумевает загрузку всей таблицы или же очень большой ее части в память, с тем, чтобы программными средствами делать постраничный просмотр. С другой стороны это может быть произведено на стороне сервера, тогда приложение получит только те данные, которые оно запросило для отображения нужной страницы. При таком подходе сокращается время на пересылку данных, постобработку и хранение их в памяти. Т.е. происходит существенное ускорение производительности приложения.
В целях эксперимента мы пропустим первые 20 000 записей и возьмем следующие 50 000.
Подход для SQL Server 2000
Я думаю что предыдущих примеров и комментариев хватает, чтобы понять работу скрипта.
Время выполнения:
SQL Server Execution Times:
CPU time = 110 ms, elapsed time = 839 ms.
Статистика ввода\вывода:
Scan count 1,
logical reads 8037,
physical reads 0,
read-ahead reads 0,
lob logical reads 0,
lob physical reads 0,
lob read-ahead reads 0.
Подход для SQL Server 2005/2008
Время выполнения:
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 631 ms.
Статистика ввода\вывода:
Scan count 1,
logical reads 530,
physical reads 0,
read-ahead reads 1549,
lob logical reads 0,
lob physical reads 0,
lob read-ahead reads 0.
Подход для SQL Server 2011
Время выполнения:
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 626 ms.
Статистика ввода\вывода:
Scan count 1,
logical reads 530,
physical reads 0,
read-ahead reads 1549,
lob logical reads 0,
lob physical reads 0,
lob read-ahead reads 0.
Наиболее интересен результат по использованию процессорного времени (CPU Time) и время выполнения (Elapsed Time — время потребовавшееся запросу на выполнение). Сравнение замеров представлено ниже:
| Sql Server Version | CPU Time | Elapsed Time |
| 2000 | 110ms | 839 ms |
| 2005/2008 | 78ms | 631 ms |
| 2011 | 46ms | 626 ms |
В таблице наглядно представлено, что новый SQL Server работает заметно быстрее по сравнению с предыдущими версиями. Естественно, что для вашей машины замеры времени могут отличаться, но производительность нового сервера будет всегда выше.
Альтернатива выражению TOP
Новые возможности Denali в некоторых ситуациях могут стать заменой выражению TOP.
Для примера возьмем ситуацию, когда необходимо получить первые 10 записей отсортированные по убыванию какого-либо параметра.
Подходы на предыдущих версиях
Подход возможный в SQL Server Denali
Как заметили в комментариях это неверный код и вернет результат обратный Top(10).
Как извлечь первые N строк в Oracle
Подготовим данные
create table test_rows as select level lvl from dual connect by level <= 10;
Получим первые две строки:
Таким образом можно ограничить количество строк, но вот проблема: rownum вычисляется до сортировки:
select * from test_rows where rownum < 3 order by lvl desc;
LVL
———-
2
1
Это можно обойти за счёт вложенного запроса:
select * from (select * from test_rows order by lvl desc) where rownum 5;
Это не самое удачное решение. Начиная с версии 12.1 появилась возможность получать top N записей проще, как и делать разделение по страницам.
Самый первый запрос, получение двух строк можно переписать в виде:
Запрос на получение второй страницы:
Также можно получить только определенный процент записей:
Также существует возможность получить больше строк, чем было запрошено, при наличии order by и дубликатов:
Увы, магии не существует и в текущих версиях fetch first при выполнении прозрачно для пользователя переписывается на использование rownum. Поэтому с точки зрения производительности разницы почти нет(есть данные что некоторые варианты подобных запросов могут использовать менее эффективную сортировку).