SQL Server SELECT TOP

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.

products

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:

SQL Server SELECT TOP - top ten most expensive products

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:

SQL Server SELECT TOP - TOP PERCENT example

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:

SQL Server SELECT TOP - TOP WITH TIES example

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.
Was this tutorial helpful?