Limits the rows returned in a query result set to a specified number of rows or percentage of rows in Querona. When TOP is used in conjunction with the ORDER BY clause, the result set is limited to the first N number of ordered rows; otherwise, it returns the first N number of rows in an undefined order. Use this clause to specify the number of rows returned from a SELECT statement.
[ TOP (expression) [PERCENT] [ WITH TIES ] ]
Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.
Indicates that the query returns only the first expression percent of rows from the result set. Fractional values are rounded up to the next integer value.
Used when you want to return two or more rows that tie for last place in the limited results set. Must be used with the ORDER BY clause. WITH TIES may cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but 2 additional rows match the values of the ORDER BY columns in row 5, the result set will contain 7 rows.
TOP…WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified. The returned order of tying records is arbitrary. ORDER BY does not affect this rule.
In a SELECT statement, always use an ORDER BY clause with the TOP clause. This is the only way to predictably indicate which rows are affected by TOP.
Use TOP instead of SET ROWCOUNT to limit the number of rows returned.
For backward compatibility, the parentheses are optional in SELECT statements. We recommend that you always use parentheses for TOP in SELECT statements for consistency.
A. Using TOP with a constant value#
The following examples use a constant value to specify the number of employees that are returned in the query result set. In the first example, the first 10 undefined rows are returned because an ORDER BY clause is not used. In the second example, an ORDER BY clause is used to return the top 10 recently hired employees.
USE AdventureWorks2012; -- Select the first 10 random employees. SELECT TOP(10)JobTitle, HireDate FROM HumanResources.Employee; -- Select the first 10 employees hired most recently. SELECT TOP(10)JobTitle, HireDate FROM HumanResources.Employee ORDER BY HireDate DESC;
B. Using TOP with a variable#
The following example uses a variable to specify the number of employees that are returned in the query result set.
USE AdventureWorks2012; DECLARE @p AS int = 10; SELECT TOP(@p)JobTitle, HireDate, VacationHours FROM HumanResources.Employee ORDER BY VacationHours DESC;
C. Specifying a percentage#
The following example uses PERCENT to specify the number of employees that are returned in the query result set. There are 290 employees in the HumanResources.Employee table. Because 5 percent of 290 is a fractional value, the value is rounded up to the next whole number.
USE AdventureWorks2012; SELECT TOP(5)PERCENT JobTitle, HireDate FROM HumanResources.Employee ORDER BY HireDate DESC;
D. Using WITH TIES to include rows that match the values in the last row#
The following example obtains the top
10 percent of all employees with the highest salary and returns them in descending order according to their salary.
WITH TIES makes sure that any employees that have salaries equal to the lowest salary returned (the last row) are also included in the result set,
even if doing this exceeds
10 percent of employees.
USE AdventureWorks2012; SELECT TOP(10) PERCENT WITH TIES pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate FROM Person.Person AS pp INNER JOIN HumanResources.Employee AS e ON pp.BusinessEntityID = e.BusinessEntityID INNER JOIN HumanResources.EmployeePayHistory AS r ON r.BusinessEntityID = e.BusinessEntityID ORDER BY Rate DESC;