{"id":95741,"date":"2023-02-06T20:39:23","date_gmt":"2023-02-06T20:39:23","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=95741"},"modified":"2026-05-08T09:31:41","modified_gmt":"2026-05-08T09:31:41","slug":"using-top-clause-in-a-select-statement","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/using-top-clause-in-a-select-statement\/","title":{"rendered":"Using TOP clause in a SELECT statement"},"content":{"rendered":"

What is the TOP clause in a SQL Server SELECT statement, and how does it work? Greg Larsen explains.<\/strong><\/p>\n

TOP Clause Syntax<\/h2>\n

The syntax for the top clause is simple and is shown in Figure 1.<\/p>\n

TOP (<expression>) [PERCENT]  \n    [ WITH TIES ]--<\/pre>\n
Figure 1: Syntax for TOP Clause<\/strong><\/p>\n
Where: <\/strong><\/p>\n
expression<\/em> – an expression that identifies the number of rows to be returned. The expression<\/em> equates to a float when the PERCENT<\/code> option is used, otherwise it equates to a bigint<\/code>.<\/p>\n
PERCENT<\/code> \u2013 When this keyword is included a percentage of rows is returned instead of specific number of rows.<\/p>\n
WITH TIES<\/code> \u2013 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<\/code> clause needs to be included when the WITH TIES<\/code> option is used. Using this option might mean more rows than the evaluated expression could be returned.<\/p>\n
This article will only discuss using the TOP<\/code> clause in a SELECT<\/code> statement. The TOP<\/code> clause can also be used in UPDATE<\/code>, INSERT<\/code>, DELETE<\/code> and MERGE<\/code> statements and in those cases will control the number of rows modified or removed. For complete syntax and use of the TOP<\/code> clause refer to the Microsoft documentation<\/a>.<\/p>\n
Sample data<\/h2>\n
The sample data for all examples in this article will be based off a table named dbo.Inventory<\/code>, that is created in the tempdb<\/code> database. That table is created and populated with data using the code in Listing 1.<\/p>\n
-- Create Sample Data\nUSE tempdb;\nGO \n\nCREATE TABLE dbo.Inventory(\n\tID int IDENTITY(1,1) NOT NULL,\n\tProductName varchar(50) NULL,\n\tQuantity int NULL,\n\tPrice money NULL,\n\tPriceChangeDate datetime NULL\n)  \nGO\n\nINSERT INTO dbo.Inventory VALUES \n('Hammer',10,12.99,SYSDATETIME()),\n('8 mm socket',5,3.99,SYSDATETIME()), \n('7 mm socket',5,3.99,SYSDATETIME()), \n('9 mm socket',5,3.99,SYSDATETIME()), \n('6 mm socket',5,3.59,SYSDATETIME()), \n('Flat head #3',15,3.99,SYSDATETIME()), \n('Flat head #1', 15,2.99,SYSDATETIME()), \n('Flat head #2',15,3.59,SYSDATETIME());\nGO<\/pre>\n
Listing 1: Creating and populating dbo.Inventory table with data<\/strong><\/p>\n
The dbo.Inventory<\/code> 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<\/code> statement. If you\u2019d 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<\/code> table in the tempdb<\/code> database on one of your test instances of SQL Server.<\/p>\n
Simple TOP Clause<\/h2>\n
The most simple and common use of the TOP<\/code> clause is to bring back a specific number of rows. The code in Listing 2 shows how to use the simple TOP<\/code> clause to bring back 5 rows from the dbo.Inventory<\/code> table.<\/p>\n
USE tempdb;\nGO\n\nSELECT TOP (5) * \nFROM dbo.Inventory;\nGO<\/pre>\n
Listing 2: Returning 5 unsorted rows using the TOP clause<\/strong><\/p>\n
When Listing 2 is execute the results in Report 1 are produced. (Without an ORDER BY<\/code> 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.)<\/p>\n
<\/p>\n
 Report 1: Results when Listing 2 is executed<\/strong><\/p>\n
Report 1 shows the 5 rows returned from the dbo.Inventory<\/code> table when Listing 2 is executed. These rows are considered unsorted because no ORDER BY<\/code> clause was specified on the SELECT<\/code> statement.<\/p>\n
To return the first 5 dbo.Inventory<\/code> rows based the sort order of ProductName<\/code> column the code in Listing 3 can be run.<\/p>\n
USE tempdb;\nGO\n\nSELECT TOP (5) * \nFROM dbo.Inventory\nORDER BY ProductName;\nGO<\/pre>\n
Listing 3: Returning 5 rows from a sorted set of rows<\/strong><\/p>\n
When the code in Listing 3 is run the results in Report 2 are displayed.<\/p>\n
\"Table\n\nDescription<\/p>\n
Report 2: Results when Listing 3 is executed.<\/strong><\/p>\n
When the ORDER BY<\/code> 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<\/code> column.<\/p>\n
In the examples so far, the number of rows returned has been based on a hard coded value. In this case the value was \u201c5\u201d. The number of rows return doesn\u2019t need to be hard coded, it can also be based on a variable.<\/p>\n
Using a variable in the TOP clause<\/h2>\n
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\u2019ll use the stored procedure creating in Listing 4.<\/p>\n
USE tempdb;\nGO\n\nCREATE PROCEDURE dbo.Get_TopX (@TopX int)\nAS\n  SELECT TOP (@TopX) * \n  FROM dbo.Inventory;\nGO\n\nEXEC dbo.Get_TopX @TopX = 2;<\/pre>\n
Listing 4: Store Procedure to return the number of rows based on variable<\/strong><\/p>\n
In Listing 4 the stored procedure dbo.Get_TopX<\/code> was created. This procedure requires a parameter named @TopX<\/code> to be passed when this stored procedure is executed. This variable is used to identify the number of rows the TOP<\/code> clause will return when the store procedure is executed. When the EXEC<\/code> statement in Listing 4 is run the results in Report 3 is produced.<\/p>\n
\"Table\n\nDescription<\/p>\n
Report 3: Results when stored procedure dbo.Get_TopX in Listing 4.<\/strong><\/p>\n
A different number of rows can be returned by just passing a different value for the input parameter for the dbo.Get_TopX<\/code> stored procedure. I\u2019ll leave it up to you to test returning different numbers of rows by passing different parameter values to the dbo.Get_TopX<\/code> stored procedure.<\/p>\n
A couple of quick notes. The TOP<\/code> clause value may not be negative, or an error will be returned. If the value is 0, then 0 rows will be returned.<\/p>\n
Additionally, you can use an expression in the TOP<\/code> clause. Change the previous procedure to TOP (@TopX -1)<\/code> and when the parameter value is 2, one row will be output.<\/p>\n
Returning a Percentage of rows using TOP clause<\/h2>\n
All the TOP<\/code> clause examples so far have returned a specific number of rows from the record set, based on the number identified in the TOP<\/code> clause. The TOP<\/code> 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<\/code> statement in Listing 5.<\/p>\n
USE tempdb;\nGO\n\nSELECT TOP (50) PERCENT *\nFROM dbo.Inventory\nWHERE ProductName like '% mm %';\nGO<\/pre>\n
Listing 5: Returning a percentage of rows using TOP clause.<\/strong><\/p>\n
The results when the code in Listing 5 is executed is show in Report 4.<\/p>\n
\"Table\n\nDescription<\/p>\n
Report 4: Results when the code in Listing 5 is executed.<\/strong><\/p>\n
The SELECT<\/code> statement in Listing 5 only returned 2 rows, which is exactly 50 percent of the metric socket tools stored in the dbo.Inventory<\/code> table.<\/p>\n
When using the PERCENT<\/code> option of the TOP<\/code> 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.<\/p>\n
USE tempdb;\nGO\n\nSELECT TOP (51) PERCENT * \nFROM dbo.Inventory\nWHERE ProductName like '%socket%';\nGO<\/pre>\n
Listing 6: Returning 51 PERCENT of the rows.<\/strong><\/p>\n
When the code in Listing 6 is run the results in Report 5 is produced.<\/p>\n
\"A<\/p>\n
Report 5: Results when Listing 6 is produced.<\/p>\n
When SQL Server processes the code in Listing 5, it finds that 4 rows that have name \u201csocket\u201d in the ProductName<\/code>. 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.<\/p>\n
Using the WITH TIES option<\/h2>\n
The TOP<\/code> clause also has a WITH TIES<\/code> 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<\/code> clause. When the WITH TIES<\/code> option is used the statement must also include an ORDER BY<\/code> clause, otherwise the following error will occur:<\/p>\n
Msg 1062, Level 15, State 1, Line 78<\/code><\/p>\n
The TOP N WITH TIES clause is not allowed without a corresponding ORDER BY clause.<\/code><\/p>\n
To show how to bring back ties consider the two SELECT<\/code> statements in Listing 7.<\/p>\n
USE tempdb;\nGO\n\nSELECT TOP (2) WITH TIES * \nFROM dbo.Inventory\nWHERE Price < 3.60\nORDER BY Price;\nGO\n\nSELECT TOP (2) * \nFROM dbo.Inventory\nWHERE Price < 3.60\nORDER BY Price;\nGO<\/pre>\n
Listing 7: Using the WITH TIES options<\/strong><\/p>\n
When the code in Listing 7 is executed, the first SELECT<\/code> statement returns the rows shown in Report 6 and the second SELECT<\/code> statement generates the rows in Report 7.<\/p>\n
\"Table\n\nDescription<\/p>\n
Report 6: Rows returned when the WITH TIES options is used<\/strong><\/p>\n
\"Table\n\nDescription<\/p>\n
Report 7: Rows returned when the WITH TIES option is not used<\/strong><\/p>\n
Both SELECT<\/code> statements in Listing 7 use the same TOP (2)<\/code> clause. The only difference between the two SELECT<\/code> statements is the first one uses the WITH TIES<\/code> option, whereas the second one doesn\u2019t use this option. By reviewing the output in Report 6 and 7 you can see the WITH TIES<\/code> options produced 3 rows, whereas the SELECT<\/code> statement without this additional option only returns 2 rows. The WITH TIES<\/code> options brought back the third row in Listing 6 because that row had the same value for the Price<\/code> column as the second row (or last rows based on the TOP<\/code> clause number).<\/p>\n
Logical processing of TOP and ORDER BY clauses<\/h2>\n
There are times when you might get some unexpected results due to the logical processing order of a TOP<\/code> and ORDER BY<\/code> clauses. These unexpected results can occur when the TOP<\/code> clause used in conjunction which the UNION<\/code>, UNION ALL<\/code>, EXCEPT<\/code> and INTERSECT<\/code> operators. To demonstrate this, the code in Listing 8 will be run. This code uses the UNION<\/code> operation.<\/p>\n
USE tempdb;\nGO\n\nSELECT TOP(1) * FROM dbo.Inventory\nWHERE ProductName like 'Flat head%'   \nUNION  \nSELECT TOP(1) * FROM dbo.Inventory \nWHERE ProductName like '%socket%'  \nORDER BY Price ASC; \nGO<\/pre>\n
Listing 8: UNION query <\/strong><\/p>\n
The intent of the code in Listing 8 is to find the least expensive \u201cFlat head\u201d and \u201csocket\u201d products. When the code in Listing 8 is run the output in Report 8 is created.<\/p>\n
\"Table\n\nDescription<\/p>\n
Report 8: Results when Listing 8 is run<\/strong><\/p>\n
The code in Listing 8 didn\u2019t display the least expensive \u201cFlat head\u201d or \u201csocket\u201d products. The reason this occurred was because the ORDER BY<\/code> clause was processed after the two SELECT<\/code> statement where run and the UNION<\/code> operation was performed.<\/p>\n
Because both SELECT<\/code> statements returned an unorder set, the TOP (1)<\/code> 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<\/code> statement the ORDER BY<\/code> clause needs to be processed prior to the TOP<\/code> clause, in each SELECT<\/code> statement. To correctly identify the least expensive \u201cFlat head\u201d and \u201csocket\u201d tools the code in Listing 9 can be used .<\/p>\n
USE tempdb;\nGO\n\nSELECT * FROM \n   (SELECT TOP(1) * FROM dbo.Inventory\n    WHERE ProductName like 'Flat head%'\n    ORDER BY Price ASC) AS Flat_Head\nUNION  \nSELECT * FROM\n   (SELECT TOP(1) * FROM dbo.Inventory\n    WHERE ProductName like '%socket%'\n    ORDER BY Price ASC) AS MM;\nGO<\/pre>\n
Listing 9: Correctly finding the least expensive items<\/strong><\/p>\n
When the code in Listing 9 is executed the results in Report 9 are displayed.<\/p>\n
\"Table\n\nDescription<\/p>\n
Report 9: Results when Listing 9 is executed.<\/strong><\/p>\n
By placing an ORDER BY<\/code> clause on the SELECT<\/code> statement of each subquery, along with the TOP<\/code> clause, the results will first be sorted, prior to selecting the TOP (1)<\/code> record, from each SELECT<\/code> statement. By having the records sorted the correct inexpensive item for each type of product is selected.<\/p>\n
Using the TOP clause in a SELECT statement<\/h2>\n
The TOP<\/code> clause can be used to limit the number of rows returned from a SELECT<\/code> statement. The number of rows returned can be an exact number, or a percentage of rows. The WITH TIES<\/code> options can be used to bring back more rows than what is specified in the TOP<\/code> 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<\/code> clause to be included. The examples in this article only covered using the TOP<\/code> clause in a SELECT<\/code> statement. The TOP<\/code> clause can also be used in an INSERT<\/code>, UPDATE<\/code>, DELETE<\/code> or MERGE<\/code> statement. For more information about using the TOP<\/code> clause refer to the Microsoft documentation.<\/p>\n","protected":false},"excerpt":{"rendered":"
There might be a time when you might want to return just a few rows of a result set, instead of the complete set. This might be useful if you want to just validate a selection criteria or a few rows of data. For whatever the reason the TOP clause can be used to return a specific number or a percentage of rows from a result set. This article will cover using the TOP clause in a SELECT statement and how it can be used to return a partial set of records.…<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143525,143531],"tags":[],"coauthors":[11330],"class_list":["post-95741","post","type-post","status-publish","format-standard","hentry","category-featured","category-learn","category-t-sql-programming-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95741","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=95741"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95741\/revisions"}],"predecessor-version":[{"id":110381,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95741\/revisions\/110381"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=95741"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=95741"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=95741"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=95741"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}