Which clause can be used with the top command to specify a number of values to return?

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 more rows with values that match the last row in the limited result set. Note that WITH TIES may cause more rows to be returned than you specify 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 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 on.

We will use the production.products table in the sample database for the demonstration.

1) Using TOP with a constant value

The following example uses a constant value 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 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 TOP WITH TIES to include rows that match the values in the last row

The following statement returns the top three most expensive products:

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 used TOP WITH TIES, it returned three more products whose list prices are the same as the third one.

In this tutorial, you have learned how to use the SQL Server SELECT TOP statement to limit the number of rows or percentage of rows returned by a query.