Learn SQL SELECT TOP statement

This SQL tutorial will understand how to use the SQL SELECT TOP queries using various examples.

A SQL TOP query retrieves the first 'n' number of rows from any table.

The syntax of the SQL SELECT TOP query is:

SELECT TOP <number of rows> FROM <table name>

Here, the TOP Clause limits the result set per the number of rows specified. For example, the following T-SQL statement returns the top 5 records from the [SalesOrderDetail] table.

SELECT TOP 5 * FROM
SalesLT.SalesOrderDetail

You can also specify the PERCENT keyword instead of specifying the numeral value with the TOP Clause. For example, suppose we require 2% of sample data from the query output. In this case, you can modify the select statement as below

SELECT TOP 2 PERCENT * FROM
SalesLT.SalesOrderDetail 
ORDER BY SalesLT.SalesOrderDetail.SalesOrderID

WITH TIES keyword with SQL TOP statement

Suppose you have multiple sales orders with a similar value for the [LineTotal] column. The [LineTotal] column is specified in the ORDER BY Clause for sorting data.

SELECT  * FROM
SalesLT.SalesOrderDetail 
ORDER BY SalesLT.SalesOrderDetail.LineTotal

As shown above, Row 3 (SalesOrderID 71938) and Row 4 (SalesOrderID 71783) has same [LineTotal] amount as 5.394000. We specified the TOP 3 clause, so the question arises - Which salesOrderID (71958 or 71783) query returns in the output?

SELECT  TOP 3 * FROM
SalesLT.SalesOrderDetail 
ORDER BY SalesLT.SalesOrderDetail.LineTotal

AS SHOWN BELOW, the SQL TOP statement returned only one row from the matching rows.

Now, if we want to include all matching rows from the last row column value, we can specify the WITH TIES keyword below.

SELECT  TOP 3 WITH TIES * FROM
SalesLT.SalesOrderDetail 
ORDER BY SalesLT.SalesOrderDetail.LineTotal

We get four records in the output despite specifying the TOP 3 keyword in the SQL SELECT TOP statement.

TOP Clause with data sort in descending order

If we want to sort data in descending order in the ORDER BY Clause, use the keyword DESC below.

SELECT  TOP 3  * FROM
SalesLT.SalesOrderDetail 
ORDER BY SalesLT.SalesOrderDetail.LineTotal DESC

Top Clause with a variable

We used SQL TOP statement with a fixed or PERCENT value in the above sections. We can also specify the value using a variable in the TOP statement.

For example, the below query uses a variable @i for specifying the number of rows required from the select statement.

DECLARE @i int
SET @i=2
SELECT 
TOP(@i) * FROM
SalesLT.SalesOrderDetail 
ORDER BY SalesLT.SalesOrderDetail.SalesOrderID

Like the Select statement, you can also use TOP Clause with UPDATE and DELETE statements.

DELETE TOP (1)
FROM SalesLT.SalesOrderDetail
UPDATE Top (10) SalesLT.SalesOrderDetail SET ORDERQty=10