In this article, we will learn how to use SQL SELECT TOP queries and we will also reinforce this learning using examples.
Introduction
The TOP clause allows us to limit the result set of the queries according to the number of rows or the percentage of rows. In general, the TOP and ORDER BY construction are used together. Otherwise, the TOP clause will return the N number of rows in an uncertain order. For this reason, it is the best practice to use the TOP clause with an ORDER BY to obtain a certain sorted result.
The syntax of the TOP clause is as follows:
|
1 2 3 4 |
SELECT TOP (expression) [PERCENT] [WITH TIES] FROM table_name |
Arguments
Expression
This numerical expression defines how many rows are returned from the query. For instance, when we want to return the first 10 rows of the table we can set this parameter as 10. In this example, we retrieve random 5 rows from the Product table.
|
1 2 3 4 |
SELECT TOP (5) Name, ProductNumber, StandardCost FROM Production.Product; |
PERCENT
The PERCENT keyword specifies that the query will return rows by %n proportion of the result set. This value must be between 0 and 100. Such as, if we want to retrieve half of the rows in a table, it would be sufficient to set this value to 50. The following query will return 20 percent of rows in the table.
|
1 2 3 4 |
SELECT TOP (20) PERCENT Name, ProductNumber, StandardCost FROM Production.Product; |
WITH TIES
The WITH TIES keyword enables to include the rows into the result set that matches with the last row. We need to take into account one point about the WITH TIES, usage of this expression in the queries may cause more rows to be returned than we specify in the TOP expression. For example, if we want to retrieve the highest cost product we can use the TOP 1 keyword. However, if we add the WITH TIES keyword to the SQL SELECT TOP statement, the query will return all rows which have the same cost. WITH TIES keyword must be used with the ORDER BY. Let’s execute the following query and then interpret the result.
|
1 2 3 4 5 |
SELECT TOP (1) WITH TIES Name, ProductNumber, StandardCost FROM Production.Product ORDER BY StandardCost DESC |
As we can see, the query has returned more than one product whose costs are the same as the first one.
Using the SQL SELECT TOP statement with a variable
Variables are database objects which are used to store data during the execution of the query. In the following query, we assign a value to the variable and the query will return the first rows that equal the variable assigned value.
|
1 2 3 4 5 6 7 8 |
equal the variable assigned value. DECLARE @Val AS INT SET @Val =3 SELECT TOP(@Val) Name, ProductNumber, StandardCost FROM Production.Product ORDER BY StandardCost DESC |
- Tip: The TOP clause accepts 0 to bigint (9223372036854775807) as an argument. For example, the following query will return an error because the argument is bigger than the maximum value of the bigint
|
1 2 3 4 5 |
SELECT TOP(9223372036854775808) Name, ProductNumber, StandardCost FROM Production.Product ORDER BY StandardCost DESC |
As has been pointed out in the above image, the query has returned an arithmetic overflow error.
Using SQL UPDATE TOP statement
When we use a TOP clause with an update statement, the update runs for the undefined rows because we could not add ORDER BY to this type of update statement. The below query demonstrates this kind of usage.
|
1 2 |
UPDATE TOP (10) Production.ProductListColors SET Color = 'Pink' |
At the same time, we can use the PERCENT keyword on the update statements. The following query will change half of the rows in the table.
|
1 2 |
UPDATE TOP (50) PERCENT Production.ProductListColors SET Color = 'Pink' |
Using SQL DELETE TOP statements
We can use the TOP statement in the delete queries. The following query deletes a random row from the ProductListColors table.
|
1 2 |
DELETE TOP (1) FROM Production.ProductListColors; |
If we want to delete the first row of the table according to a certain order, we can use CTE’s to perform this type of requirement. Through the following query, we can delete a row that has the biggest ProductID.
|
1 2 3 4 5 |
WITH DelCTE AS ( SELECT TOP 1 * FROM Production.ProductListColors ORDER BY ProductID DESC ) DELETE FROM DelCTE |
SQL SELECT TOP statement vs SET ROWCOUNT option
SET ROWCOUNT option limits the number of rows that return from a query. When we set SET ROWCOUNT as 4 for the following query, it processes the whole query but only returns 4 rows.
|
1 2 3 4 5 6 |
SET ROWCOUNT 4 SELECT Name, ProductNumber, StandardCost FROM Production.Product |
SET ROWCOUNT option overrides the TOP keyword, if the set value of the ROWCOUNT is smaller than the TOP expression, the returned number of rows will be equal to the ROWCOUNT option. For example, the following query will only return 2 rows.
|
1 2 3 4 5 6 |
SET ROWCOUNT 2 SELECT TOP(5) Name, ProductNumber, StandardCost FROM Production.Product |
The main difference between the SQL SELECT TOP statement and the SET ROWCOUNT option is that the SET ROWCOUNT option does not consider by the query optimizer and its performance might be worse than the TOP clause. Despite that, TOP clauses get involved in the query plans of the queries.
SQL SELECT TOP statement vs ROW_NUMBER function
ROW_NUMBER function helps to give temporary numbers to the result set of the query and it can be also used instead of the TOP clauses. For example, the following query will return the first 4 rows of the Product table.
|
1 2 3 4 5 6 7 8 |
SELECT * FROM ( SELECT Name, ProductNumber, StandardCost, ROW_NUMBER() OVER (ORDER BY StandardCost DESC) AS RN FROM Production.Product ) AS TMP_TBL WHERE RN<=4 |
Conclusion
In this article, we have talked about the SQL SELECT TOP statements, this query structure helps to limit the result set of the queries. At the same time, we have learned some alternatives to the TOP clauses.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023