This article is part of Greg Larsen's continuing series on Learning T-SQL. To see all the items in the series, click here.
TOP Clause Syntax
The syntax for the top clause is simple and is shown in Figure 1.
1 2 |
TOP (<expression>) [PERCENT] [ WITH TIES ]-- |
Figure 1: Syntax for TOP Clause
Where:
expression – an expression that identifies the number of rows to be returned. The expression equates to a float when the PERCENT
option is used, otherwise it equates to a bigint
.
PERCENT
– When this keyword is included a percentage of rows is returned instead of specific number of rows.
WITH TIES
– This is an optional parameter, but when specified two or more rows will be returned when they have the same value as the last limiting value in an ordered set. The ORDER BY
clause needs to be included when the WITH TIES
option is used. Using this option might mean more rows than the evaluated expression could be returned.
This article will only discuss using the TOP
clause in a SELECT
statement. The TOP
clause can also be used in UPDATE
, INSERT
, DELETE
and MERGE
statements and in those cases will control the number of rows modified or removed. For complete syntax and use of the TOP
clause refer to the Microsoft documentation.
Sample data
The sample data for all examples in this article will be based off a table named dbo.Inventory
, that is created in the tempdb
database. That table is created and populated with data using the code in Listing 1.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- Create Sample Data 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 |
Listing 1: Creating and populating dbo.Inventory table with data
The dbo.Inventory
table contains different types of tools. In the sections that follow, this table will be used to show how the TOP clause works, when used in a SELECT
statement. If you’d like to follow along and run the examples found in this article then you can use the code in Listing 1 to create the dbo.Inventory
table in the tempdb
database on one of your test instances of SQL Server.
Simple TOP Clause
The most simple and common use of the TOP
clause is to bring back a specific number of rows. The code in Listing 2 shows how to use the simple TOP
clause to bring back 5 rows from the dbo.Inventory
table.
1 2 3 4 5 6 |
USE tempdb; GO SELECT TOP (5) * FROM dbo.Inventory; GO |
Listing 2: Returning 5 unsorted rows using the TOP clause
When Listing 2 is execute the results in Report 1 are produced. (Without an ORDER BY
clause, it is possible for the output to include 5 different rows, but in such a small set that you just created, it is not likely. Variations in output are typically noticed when there is more processing involved.)
Report 1: Results when Listing 2 is executed
Report 1 shows the 5 rows returned from the dbo.Inventory
table when Listing 2 is executed. These rows are considered unsorted because no ORDER BY
clause was specified on the SELECT
statement.
To return the first 5 dbo.Inventory
rows based the sort order of ProductName
column the code in Listing 3 can be run.
1 2 3 4 5 6 7 |
USE tempdb; GO SELECT TOP (5) * FROM dbo.Inventory ORDER BY ProductName; GO |
Listing 3: Returning 5 rows from a sorted set of rows
When the code in Listing 3 is run the results in Report 2 are displayed.
Report 2: Results when Listing 3 is executed.
When the ORDER BY
clause is included, SQL Server first sorts the results set before determining which rows to return. By reviewing the output in Report 3, you can see that SQL Server returned 5 rows, and each row is sorted based on the ProductName
column.
In the examples so far, the number of rows returned has been based on a hard coded value. In this case the value was “5”. The number of rows return doesn’t need to be hard coded, it can also be based on a variable.
Using a variable in the TOP clause
There are times when you might want the number of rows returned from a specific query to be based on a variable. By doing this the number of rows returned could be different each time a specific query is executed. To demonstrate I’ll use the stored procedure creating in Listing 4.
1 2 3 4 5 6 7 8 9 10 |
USE tempdb; GO CREATE PROCEDURE dbo.Get_TopX (@TopX int) AS SELECT TOP (@TopX) * FROM dbo.Inventory; GO EXEC dbo.Get_TopX @TopX = 2; |
Listing 4: Store Procedure to return the number of rows based on variable
In Listing 4 the stored procedure dbo.Get_TopX
was created. This procedure requires a parameter named @TopX
to be passed when this stored procedure is executed. This variable is used to identify the number of rows the TOP
clause will return when the store procedure is executed. When the EXEC
statement in Listing 4 is run the results in Report 3 is produced.
Report 3: Results when stored procedure dbo.Get_TopX in Listing 4.
A different number of rows can be returned by just passing a different value for the input parameter for the dbo.Get_TopX
stored procedure. I’ll leave it up to you to test returning different numbers of rows by passing different parameter values to the dbo.Get_TopX
stored procedure.
A couple of quick notes. The TOP
clause value may not be negative, or an error will be returned. If the value is 0, then 0 rows will be returned.
Additionally, you can use an expression in the TOP
clause. Change the previous procedure to TOP (@TopX -1)
and when the parameter value is 2, one row will be output.
Returning a Percentage of rows using TOP clause
All the TOP
clause examples so far have returned a specific number of rows from the record set, based on the number identified in the TOP
clause. The TOP
clause also supports returning a percentage of the rows from the result set, instead of a specific number of rows. To show how to return a percentage of rows review the SELECT
statement in Listing 5.
1 2 3 4 5 6 7 |
USE tempdb; GO SELECT TOP (50) PERCENT * FROM dbo.Inventory WHERE ProductName like '% mm %'; GO |
Listing 5: Returning a percentage of rows using TOP clause.
The results when the code in Listing 5 is executed is show in Report 4.
Report 4: Results when the code in Listing 5 is executed.
The SELECT
statement in Listing 5 only returned 2 rows, which is exactly 50 percent of the metric socket tools stored in the dbo.Inventory
table.
When using the PERCENT
option of the TOP
clause, the calculated number of rows returned could produce a fractional value. When a fractional number is calculated SQL Server rounds up to the next integer value to determine the number of rows to return. To demonstrate this, consider the code in Listing 6.
1 2 3 4 5 6 7 |
USE tempdb; GO SELECT TOP (51) PERCENT * FROM dbo.Inventory WHERE ProductName like '%socket%'; GO |
Listing 6: Returning 51 PERCENT of the rows.
When the code in Listing 6 is run the results in Report 5 is produced.
When SQL Server processes the code in Listing 5, it finds that 4 rows that have name “socket” in the ProductName
. 51 percent of the 4 rows found produces the fractional value 2.04. Because SQL Server cannot bring back fractional rows, the value 2.04 is round up to the next integer value, which is why 3 rows were returned when Listing 6 is executed.
Using the WITH TIES option
The TOP
clause also has a WITH TIES
option. This option is useful to return all the rows that have the same value as the last row. By last row, I mean the row number that is equal to the number used in the TOP
clause. When the WITH TIES
option is used the statement must also include an ORDER BY
clause, otherwise the following error will occur:
Msg 1062, Level 15, State 1, Line 78
The TOP N WITH TIES clause is not allowed without a corresponding ORDER BY clause.
To show how to bring back ties consider the two SELECT
statements in Listing 7.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 |
Listing 7: Using the WITH TIES options
When the code in Listing 7 is executed, the first SELECT
statement returns the rows shown in Report 6 and the second SELECT
statement generates the rows in Report 7.
Report 6: Rows returned when the WITH TIES options is used
Report 7: Rows returned when the WITH TIES option is not used
Both SELECT
statements in Listing 7 use the same TOP (2)
clause. The only difference between the two SELECT
statements is the first one uses the WITH TIES
option, whereas the second one doesn’t use this option. By reviewing the output in Report 6 and 7 you can see the WITH TIES
options produced 3 rows, whereas the SELECT
statement without this additional option only returns 2 rows. The WITH TIES
options brought back the third row in Listing 6 because that row had the same value for the Price
column as the second row (or last rows based on the TOP
clause number).
Logical processing of TOP and ORDER BY clauses
There are times when you might get some unexpected results due to the logical processing order of a TOP
and ORDER BY
clauses. These unexpected results can occur when the TOP
clause used in conjunction which the UNION
, UNION ALL
, EXCEPT
and INTERSECT
operators. To demonstrate this, the code in Listing 8 will be run. This code uses the UNION
operation.
1 2 3 4 5 6 7 8 9 10 |
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 |
Listing 8: UNION query
The intent of the code in Listing 8 is to find the least expensive “Flat head” and “socket” products. When the code in Listing 8 is run the output in Report 8 is created.
Report 8: Results when Listing 8 is run
The code in Listing 8 didn’t display the least expensive “Flat head” or “socket” products. The reason this occurred was because the ORDER BY
clause was processed after the two SELECT
statement where run and the UNION
operation was performed.
Because both SELECT
statements returned an unorder set, the TOP (1)
clause just returned first row from those unordered sets. Which in this case was not the least expensive item. To correctly return the least expensive item for each SELECT
statement the ORDER BY
clause needs to be processed prior to the TOP
clause, in each SELECT
statement. To correctly identify the least expensive “Flat head” and “socket” tools the code in Listing 9 can be used .
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 |
Listing 9: Correctly finding the least expensive items
When the code in Listing 9 is executed the results in Report 9 are displayed.
Report 9: Results when Listing 9 is executed.
By placing an ORDER BY
clause on the SELECT
statement of each subquery, along with the TOP
clause, the results will first be sorted, prior to selecting the TOP (1)
record, from each SELECT
statement. By having the records sorted the correct inexpensive item for each type of product is selected.
Using the TOP clause in a SELECT statement
The TOP
clause can be used to limit the number of rows returned from a SELECT
statement. The number of rows returned can be an exact number, or a percentage of rows. The WITH TIES
options can be used to bring back more rows than what is specified in the TOP
clause, when the last row returned has multiple rows with the same value. If the rows returned need to be selected based on a specific sort order, then an ORDER BY
clause to be included. The examples in this article only covered using the TOP
clause in a SELECT
statement. The TOP
clause can also be used in an INSERT
, UPDATE
, DELETE
or MERGE
statement. For more information about using the TOP
clause refer to the Microsoft documentation.
Load comments