Как вывести top sql

Как в SQL получить первые (или последние) строки запроса? TOP или OFFSET?

Всем привет, сегодня мы поговорим о том, как в Microsoft SQL Server на языке T-SQL можно оставить только определенное количество первых строк результирующего набора данных. При этом мы рассмотрим два способа реализации этой простой задачи. Также я покажу Вам, как можно вывести, наоборот, только последние строки SQL запроса.

В языке T-SQL существует две стандартные возможности, которые позволяют нам применить фильтр к результирующему набору данных, иными словами, оставить в результате только определённое количество строк. Это могут быть первые строки, с учётом сортировки, что достаточно часто требуется при работе с базами данных на SQL, или последние строки, а также существует возможность вывести любой набор строк, например, пропустить первые строки и вывести определённое количество следующих строк.

Как я уже отметил, существует два способа фильтрации результирующего набора данных, первый – это использование фильтра TOP, и второй – это использование конструкции OFFSET-FETCH, которую мы подробно рассмотрели в отдельном материале — «OFFSET-FETCH в T-SQL – описание и примеры использования».

Получаем первые строки результата SQL запроса

Сейчас давайте я покажу, как можно вывести первые строки результирующего набора данных, сначала мы рассмотрим пример с использованием TOP, а затем сделаем то же самое только с помощью OFFSET-FETCH.

Но для начала давайте определимся с исходными данными, чтобы Вы понимали, какие данные у нас есть и что мы получаем в итоге.

Исходные данные для примеров

В качестве сервера у меня выступает Microsoft SQL Server 2016 Express. А теперь давайте представим, что у нас есть таблица TestTable и в ней содержатся следующие данные (перечень товаров с указанием цены).

Получаем первые строки запроса с помощью TOP

TOP – это инструкция T-SQL, с помощью которой можно ограничить число строк в результирующем наборе данных SQL запроса.

Синтаксис

TOP (Число строк) [PERCENT]

У инструкции TOP несколько параметров:

• Число строк – сразу после ключевого слова TOP в скобочках мы указываем число, которое будет означать количество строк в итоговом результате. В инструкции SELECT допускается указание данного числа без скобочек, однако это не рекомендуется;
• PERCENT – параметр, который говорит, что в запросе необходимо оставить не фактическое количество строк, а процент строк от общего количества, т.е. число, указанное ранее, будет означать процент, а не количество;
• WITH TIES – параметр, который говорит, что в результирующий набор необходимо включить и записи с тем же значением, что и последняя строка, в случае наличия подобных записей. Например, если Вам нужно получить 5 самых дорогих товаров, при этом на пятом месте запись с ценой 100, а на шестом месте также цена 100, так вот, без параметра WITH TIES Вам вернётся 5 строк, а если данный параметр указать — вернется 6 строк.

Фильтр TOP обычно применяется с сортировкой данных (ORDER BY), однако это необязательно, можно применять данный фильтр и без сортировки данных, только в этом случае строки будут возвращаться в произвольном порядке (так, как они хранятся).

Пример SQL запроса с TOP – выводим первые 5 строк

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

В данном случае мы указали сортировку по уменьшению цены (ORDER BY Price DESC), а также применили фильтр TOP (5), для ограничения вывода строк результирующего набора.

Пример SQL запроса с TOP и параметром WITH TIES

Сейчас давайте запустим два запроса, в обоих случаях мы будем запрашивать 4 самых дорогих товара, т.е. применим фильтр TOP (4), однако во втором запросе дополнительно мы укажем параметр WITH TIES и посмотрим на разницу итогового результата.

В итоге мы очень хорошо видим разницу, в первом случае вывелось 4 строки, а во втором 5, так как товар в 5 строке имеет точно такую же цену, как и товар в 4 строке.

Пример SQL запроса с TOP и параметром PERCENT

В этом примере давайте просто выведем 50 процентов итогового набора записей, т.е. половину. Для этого мы используем параметр PERCENT.

Так как у нас в таблице TestTable всего 8 записей, нам вывелось 4 строки, т.е. как раз 50 процентов.

Получаем первые строки запроса с помощью OFFSET-FETCH

Вторым способом получения первых строк является использование конструкции OFFSET-FETCH, однако она появилась только в 2012 версии SQL сервер, до этого, соответственно, этот способ использовать не получится.

У конструкции OFFSET-FETCH отсутствуют такие параметры, как PERCENT и WITH TIES, которые есть у фильтра TOP, однако у OFFSET-FETCH есть одно очень важное преимущество – это возможность пропускать определенное количество первых строк.

Примечание! OFFSET-FETCH — это часть конструкции ORDER BY, поэтому без сортировки использовать OFFSET-FETCH не удастся. Также не получится одновременно использовать OFFSET-FETCH и TOP в одном запросе SELECT.

Пример SQL запроса с OFFSET-FETCH — выводим первые 5 строк

Чтобы вывести первые строки с помощью конструкции OFFSET-FETCH, нам нужно в секции OFFSET указать 0, т.е. начинать вывод сразу с первой строки (если указать другое число, то именно такое количество строк будет пропущено). В секции FETCH мы соответственно указываем 5.

Результат, мы видим, точно такой же, как и в случае с TOP.

Как вывести последние строки SQL запроса?

Если Вам нужно получить не первые строки результирующего набора данных, а последние (например, последние записи в таблице), причем с той же самой сортировкой, то Вы также можете использовать два способа, т.е. и TOP, и OFFSET. В обоих случаях нам нужно будет немного усложнить запросы.

Получаем последние строки SQL запроса с помощью TOP

В случае с TOP нам дополнительно потребуется использовать конструкцию WITH (CTE – обобщенное табличное выражение), для того чтобы выполнить сортировку по идентификатору для применения фильтра TOP, т.е. отобрать самые последние записи. А после этого мы уже можем отсортировать строки так, как нам нужно.

Как видите, нам вывелись 5 последних строк.

Получаем последние строки SQL запроса с помощью OFFSET-FETCH

Для получения последних строк с помощью OFFSET-FETCH нам потребуется предварительно узнать общее количество строк, для того чтобы определить, сколько строк нужно пропустить. Это можно сделать как с помощью вложенного запроса, так и с помощью предварительного сохранения нужного нам значения в переменной. Я покажу способ с использованием переменной.

Итоговый результат такой же, как и в запросе с TOP.

Теперь Вы знаете, как с помощью TOP и OFFSET получать первые и последние строки результирующего набора данных, который возвращает SQL запрос.

В данной статье мы затронули одну очень маленькую возможность языка T-SQL, но их, как Вы понимаете, гораздо больше, поэтому, если Вы начинающий программист и хотите изучить язык T-SQL, то рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать на T-SQL.

У меня на этом все, удачи в освоении языка T-SQL!

Источник

SQL Инструкция SELECT TOP, LIMIT, ROWNUM

SQL SELECT TOP

Инструкция SELECT TOP используется для указания количества возвращаемых записей.

Инструкция SELECT TOP полезно для больших таблиц с тысячами записей. Возврат большого количества записей может повлиять на производительность.

Примечание: Не все базы данных поддерживают SELECT TOP. MySQL поддерживает предложение LIMIT для выбора ограниченного числа записей, в то время как Oracle использует ROWNUM.

Синтаксис SQL Server / MS Access:

Синтаксис MySQL:

Синтаксис Oracle:

Демо база данных

Ниже приведен выбор из таблицы «Customers» в образце базы данных Northwind:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1

Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany 2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico 3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico 4

Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK 5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

Примеры SQL TOP, LIMIT и ROWNUM

Следующая инструкция SQL выбирает первые три записи из таблицы «Customers»:

Пример

Следующий оператор SQL показывает эквивалентный пример использования предложения LIMIT:

Пример

Следующая инструкция SQL показывает соответствующий пример использования параметра ROWNUM:

Пример

Пример SQL TOP PERCENT

Следующая инструкция SQL выбирает первые 50% записей из таблицы «Customers»:

Пример

Добавить WHERE

Следующая инструкция SQL выбирает первые три записи из таблицы «Customers», где страна — «Germany»:

Пример

Следующий оператор SQL показывает эквивалентный пример использования LIMIT:

Пример

Следующая инструкция SQL показывает соответствующий пример использования параметра ROWNUM:

Источник

TOP (Transact-SQL)

Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server. When you use TOP with the ORDER BY clause, the result set is limited to the first N number of ordered rows. Otherwise, TOP returns the first N number of rows in an undefined order. Use this clause to specify the number of rows returned from a SELECT statement. Or, use TOP to specify the rows affected by an INSERT, UPDATE, MERGE, or DELETE statement.

Transact-SQL Syntax Conventions

Syntax

Following is the syntax for SQL Server and Azure SQL Database:

Following is syntax for Azure Synapse Analytics and Analytics Platform System (PDW):

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Arguments

expression
The numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if you specify PERCENT. Otherwise, expression is converted to bigint.

PERCENT
Indicates that the query returns only the first expression percent of rows from the result set. Fractional values are rounded up to the next integer value.

WITH TIES
Returns two or more rows that tie for last place in the limited results set. You must use this argument with the ORDER BY clause. WITH TIES might cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but two additional rows match the values of the ORDER BY columns in row 5, the result set will contain seven rows.

You can specify the TOP clause with the WITH TIES argument only in SELECT statements, and only if you’ve also specified the ORDER BY clause. The returned order of tying records is arbitrary. ORDER BY doesn’t affect this rule.

Best Practices

In a SELECT statement, always use an ORDER BY clause with the TOP clause. Because, it’s the only way to predictably indicate which rows are affected by TOP.

Use OFFSET and FETCH in the ORDER BY clause instead of the TOP clause to implement a query paging solution. A paging solution (that is, sending chunks or «pages» of data to the client) is easier to implement using OFFSET and FETCH clauses. For more information, see ORDER BY Clause (Transact-SQL).

Use TOP (or OFFSET and FETCH) instead of SET ROWCOUNT to limit the number of rows returned. These methods are preferred over using SET ROWCOUNT for the following reasons:

• As a part of a SELECT statement, the query optimizer can consider the value of expression in the TOP or FETCH clauses during query optimization. Because you use SET ROWCOUNT outside of a statement that runs a query, its value can’t be considered in a query plan.

Compatibility Support

For backward compatibility, the parentheses are optional in SELECT statements if the expression is an integer constant. We recommend that you always use parentheses for TOP in SELECT statements. Doing so provides consistency with its required use in INSERT, UPDATE, MERGE, and DELETE statements.

Interoperability

The TOP expression doesn’t affect statements that might run because of a trigger. The inserted and deleted tables in the triggers return only the rows that are truly affected by the INSERT, UPDATE, MERGE, or DELETE statements. For example, if an INSERT TRIGGER fires as the result of an INSERT statement that used a TOP clause.

SQL Server allows for updating rows through views. Because you can include the TOP clause in the view definition, certain rows may disappear from the view if the rows no longer meet the requirements of the TOP expression due to an update.

When specified in the MERGE statement, the TOP clause applies after the entire source table and the entire target table are joined. And, the joined rows that don’t qualify for an insert, update, or delete action are removed. The TOP clause further reduces the number of joined rows to the specified value and the insert, update, or delete actions apply to the remaining joined rows in an unordered way. That is, there’s no order in which the rows are distributed among the actions defined in the WHEN clauses. For example, if specifying TOP (10) affects 10 rows, of these rows, seven may be updated and three inserted. Or, one may be deleted, five updated, and four inserted, and so on. Because the MERGE statement does a full table scan of both the source and target tables, I/O performance can be affected when you use the TOP clause to modify a large table by creating multiple batches. In this scenario, it’s important to ensure that all successive batches target new rows.

Use caution when you’re specifying the TOP clause in a query that contains a UNION, UNION ALL, EXCEPT, or INTERSECT operator. It’s possible to write a query that returns unexpected results because the order in which the TOP and ORDER BY clauses are logically processed isn’t always intuitive when these operators are used in a select operation. For example, given the following table and data, assume that you want to return the least expensive red car and the least expensive blue car. That is, the red sedan and the blue van.

To achieve these results, you might write the following query.

Following is the result set.

The unexpected results are returned because the TOP clause logically runs before the ORDER BY clause, which sorts the results of the operator (UNION ALL in this case). So, the previous query returns any one red car and any one blue car and then orders the result of that union by the price. The following example shows the correct method of writing this query to achieve the desired result.

By using TOP and ORDER BY in a subselect operation, you ensure that the results of the ORDER BY clause are applied to the TOP clause and not to sorting the result of the UNION operation.

Here is the result set.

Limitations and Restrictions

When you use TOP with INSERT, UPDATE, MERGE, or DELETE, the referenced rows aren’t arranged in any order. And, you can’t directly specify the ORDER BY clause in these statements. If you need to use TOP to insert, delete, or modify rows in a meaningful chronological order, use TOP with an ORDER BY clause specified in a subselect statement. See the following Examples section in this article.

You can’t use TOP in an UPDATE and DELETE statements on partitioned views.

You can’t combine TOP with OFFSET and FETCH in the same query expression (in the same query scope). For more information, see ORDER BY Clause (Transact-SQL).

Examples

Category	Featured syntax elements
Basic syntax	TOP • PERCENT
Including tie values	WITH TIES
Limiting the rows affected by DELETE, INSERT, or UPDATE	DELETE • INSERT • UPDATE

Basic syntax

Examples in this section demonstrate the basic functionality of the ORDER BY clause using the minimum required syntax.

A. Using TOP with a constant value

The following examples use a constant value to specify the number of employees that are returned in the query result set. In the first example, the first 10 undefined rows are returned because an ORDER BY clause isn’t used. In the second example, an ORDER BY clause is used to return the top 10 recently hired employees.

B. Using TOP with a variable

The following example uses a variable to specify the number of employees that are returned in the query result set.

C. Specifying a percentage

The following example uses PERCENT to specify the number of employees that are returned in the query result set. There are 290 employees in the HumanResources.Employee table. Because five percent of 290 is a fractional value, the value is rounded up to the next whole number.

Including tie values

A. Using WITH TIES to include rows that match the values in the last row

The following example gets the top 10 percent of all employees with the highest salary and returns them in descending order according to their salary. Specifying WITH TIES ensures that employees with salaries equal to the lowest salary returned (the last row) are also included in the result set, even if it exceeds 10 percent of employees.

Limiting the rows affected by DELETE, INSERT, or UPDATE

A. Using TOP to limit the number of rows deleted

When you use a TOP (n) clause with DELETE, the delete operation is done on an undefined selection of n number of rows. That is, the DELETE statement chooses any (n) number of rows that meet the criteria defined in the WHERE clause. The following example deletes 20 rows from the PurchaseOrderDetail table that have due dates earlier than July 1, 2002.

If you want to use TOP to delete rows in a meaningful chronological order, use TOP with ORDER BY in a subselect statement. The following query deletes the 10 rows of the PurchaseOrderDetail table that have the earliest due dates. To ensure that only 10 rows are deleted, the column specified in the subselect statement ( PurchaseOrderID ) is the primary key of the table. Using a nonkey column in the subselect statement may result in the deletion of more than 10 rows if the specified column contains duplicate values.

B. Using TOP to limit the number of rows inserted

The following example creates the table EmployeeSales and inserts the name and year-to-date sales data for the top five employees from the table HumanResources.Employee . The INSERT statement chooses any five rows returned by the SELECT statement that meet the criteria defined in the WHERE clause. The OUTPUT clause displays the rows that are inserted into the EmployeeSales table. Notice that the ORDER BY clause in the SELECT statement isn’t used to determine the top five employees.

If you want to use TOP to insert rows in a meaningful chronological order, use TOP with ORDER BY in a subselect statement. The following example show how to do this. The OUTPUT clause displays the rows that are inserted into the EmployeeSales table. Notice that the top five employees are now inserted based on the results of the ORDER BY clause instead of undefined rows.

C. Using TOP to limit the number of rows updated

The following example uses the TOP clause to update rows in a table. When you use a TOP (n) clause with UPDATE, the update operation runs on an undefined number of rows. That is, the UPDATE statement chooses any (n) number of rows that meet the criteria defined in the WHERE clause. The following example assigns 10 customers from one salesperson to another.

If you have to use TOP to apply updates in a meaningful chronology, you must use TOP together with ORDER BY in a subselect statement. The following example updates the vacation hours of the 10 employees with the earliest hire dates.

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

The following example returns the top 31 rows that match the query criteria. The ORDER BY clause ensures that the 31 returned rows are the first 31 rows based on an alphabetical ordering of the LastName column.

Using TOP without specifying ties.

Result: 31 rows are returned.

Using TOP, specifying WITH TIES.

Result: 33 rows are returned, because three employees named Brown tie for the 31st row.

Источник