SQL-Ex blog

Синтаксис предложения TOP

Синтаксис предложения TOP простой:

TOP () [PERCENT]  

    [ WITH TIES ]

Где

expression - выражение, которое определяет число возвращаемых строк. Выражение оценивается как float, если используется опция PERCENT, в противном случае - bigint.

PERCENT - когда указано, возвращается процентное отношение строк, а не их конкретное число.

WITH TIES – это необязательный параметр, но при использовании будут возвращаться две или более строк, которые имеют то же самое значение, что и последнее значение в упорядоченном ограниченном наборе. Предложение ORDER BY должно быть включено, если используется опция WITH TIES. Использование этой опции может означать, что возвращаться может больше строк, чем дает вычисленное выражение expression.

Здесь мы будем обсуждать использование предложения TOP только в операторе SELECT. Однако предложение TOP также может использоваться в операторах UPDATE, INSERT, DELETE и MERGE, и в этих случаях оно будет контролировать число модифицируемых или удаляемых строк. Обратитесь к документации Microsoft за получением полной информации о синтаксисе и использовании предложения TOP.

Данные для примеров

Тестовые данные для всех примеров этой статьи будут базироваться на таблице dbo.Inventory, которая создается в базе данных tempdb. Эта таблица создается и заполняется данными с помощью кода в листинге 1.

-- Создаем тестовые данные

USE tempdb;

GO 

 CREATE TABLE dbo.Inventory(

	ID int IDENTITY(1,1) NOT NULL,

	ProductName varchar(50) NULL,

	Quantity int NULL,

	Price money NULL,

	PriceChangeDate datetime NULL

)  

GO

 INSERT INTO dbo.Inventory VALUES 

('Hammer',10,12.99,SYSDATETIME()),

('8 mm socket',5,3.99,SYSDATETIME()), 

('7 mm socket',5,3.99,SYSDATETIME()), 

('9 mm socket',5,3.99,SYSDATETIME()), 

('6 mm socket',5,3.59,SYSDATETIME()), 

('Flat head #3',15,3.99,SYSDATETIME()), 

('Flat head #1', 15,2.99,SYSDATETIME()), 

('Flat head #2',15,3.59,SYSDATETIME());

GO

Листинг 1. Создание и заполнение данными таблицы dbo.Inventory

Таблица dbo.Inventory содержит различные типы инструментов. В следующем разделе эта таблица будет использоваться для демонстрации работы предложения TOP в операторе SELECT. Если вы хотите выполнять примеры из этой статьи, то можете использовать код в листинге 1 для создания таблицы dbo.Inventory в базе данных tempdb на одном из ваших тестовых экземпляров SQL Server.

Простое предложение TOP

Наиболее простой и обычный способ использования предложения TOP заключается в выборке конкретного числа строк. Код в листинге 2 показывает использование простого предложения TOP для получения 5 строк из таблицы dbo.Inventory.

USE tempdb;

GO

SELECT TOP (5) * 

FROM dbo.Inventory;

GO

Листинг 2. Возврат 5 несортированных строк с помощью предложения TOP

При выполнении кода в листинге 2 получен результат, представленный в Отчете 1. (Без использования предложения ORDER BY возможен вывод других 5 строк, но для такого небольшого набора данных, который мы создали, это маловероятно. Вариации в выводе обычно заметны, когда обрабатывается больше строк.)


Отчет 1. Результаты выполнения листинга 1

В отчете 1 показаны 5 строк, полученных из таблицы dbo.Inventory при выполнении листинга 2. Эти строки несортированы, поскольку предложение ORDER BY не было задано в операторе SELECT.

Для возвращения первых 5 строк из таблицы dbo.Inventory на основе сортировки по столбцу ProductName можно выполнить код в листинге 3.

USE tempdb;

GO

SELECT TOP (5) * 

FROM dbo.Inventory

ORDER BY ProductName;

GO

Листинг 3. Получение 5 строк из отсортированного набора

При выполнении кода в листинге 3 будет получен следующий результат:


Отчет 2. Результаты выполнения листинга 3

Если предложение ORDER BY включено в запрос, SQL Server сначала сортирует результирующий набор, прежде чем определить возвращаемые строки. Если посмотреть вывод в Отчете 3, можно увидеть, что SQL Server вернул 5 строк, и каждая строка отсортирована на основе столбца ProductName.

До сих пор в примерах число возвращаемых строк было основано на жестко закодированном значении. В данном случае этим числом было "5". Число возвращаемых строк не обязательно жестко кодировать, его можно получить из переменной.

Использование переменной в предложении TOP

Иногда вам может потребоваться вернуть из конкретного запроса число строк на основе переменной. При этом число возвращаемых строк может разниться при всяком выполнении запроса. Для демонстрации я буду использовать хранимую процедуру, создаваемую в листинге 4.

USE tempdb;

GO

 CREATE PROCEDURE dbo.Get_TopX (@TopX int)

AS

  SELECT TOP (@TopX) * 

  FROM dbo.Inventory;

GO

EXEC dbo.Get_TopX @TopX = 2;

Листинг 4. Хранимая процедура для возвращения числа строк на основе переменной

В листинге была создана хранимая процедура dbo.Get_TopX. Эта процедура требует передачи параметра с именем @TopX при выполнении. Эта переменная используется для определения числа строк, которое будет возвращать предложение TOP при выполнении хранимой процедуры. При выполнении оператора EXEC в листинге 4 будут получены результаты, показанные в Отчете 4.


Отчет 4. Результаты, полученные при выполнении хранимой процедуры dbo.Get_TopX в листинге 4

Различное число строк может возвращаться просто передачей различных значений для входного параметра хранимой процедуры dbo.Get_TopX. Я оставлю вам протестировать это.

Пара небольших замечаний. Значение в предложении TOP не может быть отрицательным, или будет возвращаться ошибка. Если значением является 0, то будет возвращено 0 строк.

Кроме того, вы можете использовать выражение в предложении TOP. Замените в предыдущей процедуре на TOP (@TopX -1), и тогда при передаче значения параметра 2 на выходе будет одна строка.

Возвращение процентного отношения строк при использовании предложения TOP

Пока все примеры с предложением TOP возвращали заданное число строк из набора записей на основе числа, указанного в предложении TOP. Это предложением поддерживает также возвращение процентного отношения строк из результирующего набора, а не заданного числа строк. Как это делается показано в листинге 5 на примере оператора SELECT.

USE tempdb;

GO

SELECT TOP (50) PERCENT *

FROM dbo.Inventory

WHERE ProductName like '% mm %';

GO

Листинг 5. Возвращение процентного отношения числа строк при использовании предложения TOP

Результаты выполнения кода листинга 5 показаны в Отчете 4.


Результаты выполнения кода листинга 5

Оператор SELECT из листинга 5 вернул только 2 строки, что точно составляет 50 процентов от числа инструментов для метрических головок, находящихся в таблице dbo.Inventory.

При использовании опции PERCENT в предложении TOP вычисленное число возвращаемых строк может иметь дробное значение. При вычислении дробного числа SQL Server округляет его к следующему целому значению, чтобы получить число возвращаемых строк. Для демонстрации рассмотрим код в листинге 6.

USE tempdb;

GO

SELECT TOP (51) PERCENT * 

FROM dbo.Inventory

WHERE ProductName like '%socket%';

GO

Листинг 6. Возвращается 51 процент строк

При выполнении кода в листинге 6 получаются следующие результаты.


Отчет 5. Результаты при выполнении листинга 6

Когда SQL Server обрабатывает код в листинге 5, он обнаруживает, что 4 строки имеют название “socket” в ProductName. 51 процент от 4 строк дает дробное значение 2.04. Поскольку SQL Server не может вернуть дробное число строк, значение округляется к следующему целому значению - вот почему было получено 3 строк при выполнении листинга 6.

Использование опции WITH TIES

Предложение TOP имеет также опцию WITH TIES. Эта опция полезна для возвращения всех строк, которые имеют то же самое значение, что и последняя строка. Под последней строкой я имею в виду номер строки, который равен числу в предложении TOP. При использовании опции WITH TIES оператор должен также включать предложение ORDER BY, в противном случае возникнет следующая ошибка:

Msg 1062, Level 15, State 1, Line 78
The TOP N WITH TIES clause is not allowed without a corresponding ORDER BY clause.

Чтобы увидеть, как вернуть связанные записи, рассмотрите два оператора SELECT в листинге 7.

USE tempdb;

GO

SELECT TOP (2) WITH TIES * 

FROM dbo.Inventory

WHERE Price < 3.60

ORDER BY Price;

GO

SELECT TOP (2) * 

FROM dbo.Inventory

WHERE Price < 3.60

ORDER BY Price;

GO

Листинг 7. Использование опции WITH TIES

При выполнении кода в листинге 7 первый оператор SELECT возвращает строки, показанные в отчете 6, а второй оператор SELECT - в отчете 7.


Отчет 6. Возвращаемые строки, когда используется опция WITH TIES


Отчет 7. Возвращаемые строки, когда опция WITH TIES не используется

Оба оператора SELECT в листинге 7 используют одинаковое предложение TOP (2). Единственное отличие состоит в том, что в первом операторе SELECT используется опция WITH TIES, а во втором - нет. По результатам в отчетах 6 и 7 можно увидеть, что опция WITH TIES приводит к выводу 3 строк, а без нее возвращается только 2 строки. Опция WITH TIES возвращает третью строку в листинге 6 потому, что эта строка имеет тот же самое значение в столбце Price, что и вторая строка (или последняя строка на основе числа в предложении TOP).

Логическая обработка предложений TOP и ORDER BY

Иногда вы можете получить в чем-то неожиданные результаты, обусловленные логическим порядком обработки предложений TOP и ORDER BY. Эти неожиданные результаты могут иметь место, когда предложение TOP используется в сочетании с операторами UNION, UNION ALL, EXCEPT и INTERSECT. Для демонстрации выполним код в листинге 8. Этот код использует операцию UNION.

USE tempdb;

GO

SELECT TOP(1) * FROM dbo.Inventory

WHERE ProductName like 'Flat head%'   

UNION  

SELECT TOP(1) * FROM dbo.Inventory 

WHERE ProductName like '%socket%'  

ORDER BY Price ASC; 

GO

Листинг 8. Запрос с UNION

Намерением кода в листинге 8 было найти наименее дороге продукты “Flat head” и “socket”. При выполнении кода в листинге 8 получены результаты, показанные в отчете 8.


Отчет 8. Результаты выполнения кода в листинге 8

Код в листинге 8 не выводит наименее дорогие продукты “Flat head” или “socket”. Причина заключается в том, что, поскольку предложение ORDER BY обрабатывается после выполнения двух операторов SELECT и операции UNION.

Поскольку оба оператора SELECT возвращают несортированный набор, предложение TOP (1) просто возвращает первую строку этого неупорядоченного набора. В данном случае это не был наименее дорогой элемент. Чтобы получить результаты, необходимо обработать предложение ORDER BY для каждого оператора SELECT до выполнения предложения TOP в каждом операторе. Для правильного нахождения самых дешевых инструментов “Flat head” и “socket” может использоваться код в листинге 9.

USE tempdb;

GO

SELECT * FROM 

   (SELECT TOP(1) * FROM dbo.Inventory

    WHERE ProductName like 'Flat head%'

    ORDER BY Price ASC) AS Flat_Head

UNION  

SELECT * FROM

   (SELECT TOP(1) * FROM dbo.Inventory

    WHERE ProductName like '%socket%'

    ORDER BY Price ASC) AS MM;

GO

Листинг 9. Правильное нахождение наименее дорогих продуктов

При выполнении кода в листинге 9 будут получены следующие результаты.


Отчет 9. Результаты выполнения кода в листинге 9

Добавляя предложение ORDER BY в оператор SELECT каждого подзапроса, наряду с предложением TOP, мы сначала выполняем сортировку до выборки записи TOP (1) из каждого оператора. Имея отсортированные данные, для каждого типа продуктов выбрается корректный самый дешевый пункт.

Использование предложения TOP в операторе SELECT

Предложение TOP может использоваться для ограничения числа возвращаемых строк из оператора SELECT. Число возвращаемых строк может быть точным числом или процентным отношением. Опция WITH TIES может использоваться для возврата большего числа строк, чем указано в предложении TOP, когда последняя возвращаемая строка имеет то же значение, что и несколько последующих строк. Если требуется выбрать строки на основе некоторого порядка, то должен быть включено предложение ORDER BY. Примеры в данной статье относятся только к использованию предложения TOP в операторе SELECT. Предложение TOP может также применяться к операторам INSERT, UPDATE, DELETE или MERGE. За более подробной информацией обратитесь к документации Microsoft.

Ссылки по теме

1. Советы по производительности оператора SQL TOP


2. SQL ORDER BY: 5 правил сортировки на профессиональном уровне


3. Любопытный случай сортировки с Top N


4. Получение максимального значения на группу в SQL Server: Row Number против Cross Apply