Summary: in this tutorial, you will learn how to use the SQL Server SELECT TOP
statement to limit the rows returned by a query.
Introduction to SQL Server SELECT TOP
The SELECT TOP
clause allows you to limit the number of rows or percentage of rows returned in a query result set.
Because the order of rows stored in a table is unspecified, the SELECT TOP
statement is always used in conjunction with the ORDER BY
clause. Therefore, the result set is limited to the first N
number of ordered rows.
The following shows the syntax of the TOP
clause with the SELECT
statement:
SELECT TOP (expression) [PERCENT]
[WITH TIES]
FROM
table_name
ORDER BY
column_name;
Code language: SQL (Structured Query Language) (sql)
In this syntax, the SELECT
statement can have other clauses such as WHERE
, JOIN
, HAVING
, and GROUP BY
.
expression
Following the TOP
keyword is an expression that specifies the number of rows to be returned. The expression is evaluated to a float value if PERCENT
is used, otherwise, it is converted to a BIGINT
value.
PERCENT
The PERCENT
keyword indicates that the query returns the first N
percentage of rows, where N
is the result of the expression
.
WITH TIES
The WITH TIES
allows you to return additional rows with values that match those of the last row in the limited result set. Note that WITH TIES
may result in more rows being returned than specified in the expression.
For example, if you want to return the most expensive products, you can use the TOP 1
. However, if two or more products have the same prices as the most expensive product, then you may miss the other most expensive products in the result set.
To avoid this, you can use TOP 1 WITH TIES
. It will include not only the first expensive product but also the second one, and so forth.
SQL Server SELECT TOP examples
We will use the production.products
table in the sample database for the demonstration.
1) Using SQL Server SELECT TOP with a constant value
The following example uses SELECT TOP with a constant to return the top 10 most expensive products:
SELECT TOP 10
product_name,
list_price
FROM
production.products
ORDER BY
list_price DESC;
Code language: SQL (Structured Query Language) (sql)
Here is the result:
2) Using SELECT TOP to return a percentage of rows
The following example uses PERCENT
to specify the number of products returned in the result set. The production.products
table has 321
rows, therefore, one percent of 321
is a fraction value ( 3.21
), SQL Server rounds it up to the next whole number which is four ( 4
) in this case.
SELECT TOP 1 PERCENT
product_name,
list_price
FROM
production.products
ORDER BY
list_price DESC;
Code language: SQL (Structured Query Language) (sql)
The output is:
3) Using SELECT TOP WITH TIES to include rows that match the values in the last row
The following statement uses the SELECT TOP WITH TIES
to retrieve the top three most expensive products fro the products
table:
SELECT TOP 3 WITH TIES
product_name,
list_price
FROM
production.products
ORDER BY
list_price DESC;
Code language: SQL (Structured Query Language) (sql)
The output is as follows:
In this example, the third expensive product has a list price of 6499.99
. Because the statement uses TOP WITH TIES
, it returns three more products whose list prices are the same as the third one.
Summary
- Use the SQL Server
SELECT TOP
statement to limit the number of rows or percentage of rows returned by a query.