TOP#

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.

Syntax#

[
    TOP (expression) [PERCENT]
    [ WITH TIES ]
]

Arguments#

expression

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.

PERCENT

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.

WITH TIES

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.

Best Practices#

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.

Compatibility Support#

For backward compatibility, the parentheses are optional in SELECT statements. We recommend that you always use parentheses for TOP in SELECT statements for consistency.

Examples#

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. Specifying 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;

TOP

Contents