SQL Server – Limit Rows with SELECT TOP

In this SQL Server tutorial, you’ll learn how to use the SELECT TOP statement inside a T-SQL statement in SQL Server along with its syntax, examples and use cases.

The SELECT TOP statement in SQL Server allows the SQL developers to limit the number of records or percentage of records that is returned when the query is executed.

Syntax

SELECT TOP (topvalue) [ PERCENT ] [ WITH TIES ]
expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

Parameters

• TOP (topvalue)
  • This returns the top number of records or rows in a result set. The number of records depends on the value specified in the parameter topvalue.
• PERCENT
  • This is an optional parameter. When PERCENT is specified, the TOP rows that you have specified above takes the specified percentage of the entire records.
• WITH TIES
  • This is an optional parameter. This allows you to return more records with the values that match the last row in the limited set. This may result in more records being returned than what is specified with the TOP statement. One of the examples or use case of this is when you want to return the top 1 student who has got more marks. If you use TOP 1 to query the students for maximum marks, you might lose out other students records who have also got the same marks as the top student. To avoid this issue, you can use TOP 1 WITH TIES.

Let’s look at the Person.Person table in the AdventureWorks database and try out the SELECT TOP Query.

1. Using SELECT TOP with constant value

Below is a sample query that returns the TOP 10 Person records ordered by their FirstName.

SELECT TOP 10 FirstName
FROM [Person].[Person] 
ORDER BY FirstName

2. Using SELECT TOP WITH TIES

In the below query, we have included the WITH TIES clause along with the SELECT TOP.

SELECT TOP 10  WITH TIES FirstName
FROM [Person].[Person] 
ORDER BY FirstName

Although we have only 10 records in the SELECT TOP, the WITH TIES will also return all the Names with the same value that is found in TOP 10 records. As a result, you will see more records being returned that what is specified in the SELECT TOP 10.

3. Using TOP PERCENT

Let’s have a look at an SQL query that uses PERCENT to specify the percentage of results returned when the query is run. The table contains 19972 records and the below query returns the first 10% of the records from that 19972 records.

SELECT TOP(10) PERCENT *
FROM [Person].[Person] 

The other 90% of the result set will not be returned when the above query is run.