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.
Report 5: Results when Listing 6 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