Esat Erkec

SQL SELECT TOP statement overview and examples

January 25, 2021 by

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:

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.

Usage of the TOP clause in T-SQL

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.

The PERCENT keyword usage with TOP clause

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.

The WITH TIES keyword usage with TOP clause

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.

The SQL variable keyword usage with TOP clause

  • 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

Arithmetic overflow issue in the SELECT TOP statement

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.

Usage of the TOP clause in the update queries.

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.

The PERCENT keyword usage with an update statement

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.

Usage of the TOP clause in the delete queries.

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.

Use CTE to delete rows in a table

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.

SET ROWCOUNT vs TOP clause

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.

SET ROWCOUNT vs TOP clause difference

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.

TOP Statement vs ROW_NUMBER Function

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.

Esat Erkec
Latest posts by Esat Erkec (see all)
SQL commands, T-SQL

About Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert. 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

3,423 Views