TOP (Transact-SQL)

Specifies that only the first set of rows will be returned from the query result. The set of rows can be either a number or a percent of the rows. The TOP expression can be used in SELECT, INSERT, UPDATE, and DELETE statements.

Transact-SQL Syntax Conventions

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.

  Parentheses that delimit expression in TOP is required in INSERT, UPDATE, and DELETE statements. For backward compatibility, TOP expression without parentheses in SELECT statements is supported, but we do not recommend this.

  If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.

• PERCENT
  Indicates that the query returns only the first expression percent of rows from the result set.

• WITH TIES
  Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.

  Note

  The returned order of tying records is arbitrary. SORT BY does not affect this rule.

Remarks

TOP cannot be used together with UPDATE and DELETE statements on partitioned views.

The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order. TOP n returns n random rows. For example, the following INSERT statement contains the ORDER BY clause, and yet this clause does not affect the rows directly referenced by the INSERT statement.

INSERT TOP (2) INTO Table2 (ColumnB) 
     SELECT ColumnA FROM Table1 
     ORDER BY ColumnA

The ORDER BY clause in the previous query references only the rows that are returned by the nested SELECT statement. The INSERT statement chooses any two rows returned by the SELECT statement. To make sure that the top two rows from the SELECT subquery are inserted, rewrite the query as follows.

INSERT INTO Table2 (ColumnB) 
     SELECT TOP (2) ColumnA FROM Table1 
     ORDER BY ColumnA

Microsoft SQL Server 2005 allows for updating views that were created with the TOP clause. Because the TOP clause is included in the view definition, certain rows may disappear from the view because of an update, if the result no longer meets the requirements of the TOP expression. For more information, see Modifying Data Through a View.

The TOP expression in a query does not affect statements that may be executed because of a trigger fired. The inserted and updated tables in the triggers will show only the rows that are truly affected by the INSERT, UPDATE, or DELETE statements.

Examples

A. Using TOP with variables

The following example uses a variable to obtain the first 10 employees that are listed in the dbo.Employee table of the AdventureWorks database.

USE AdventureWorks ;
GO
DECLARE @p AS int
SET @p='10'
SELECT TOP(@p)*
FROM HumanResources.Employee;
GO

B. Using TOP with PERCENT and WITH TIES

The following example obtains the top 10 percent of all employees with the highest salary and returns them in descending order according to salary base rate. Specifying WITH TIES makes sure that any employees that have salaries equal to the lowest salary returned are also included in the result set, even if doing this exceeds 10 percent of employees.

USE AdventureWorks ;
GO
SELECT TOP(10) PERCENT WITH TIES
c.FirstName, c.LastName, e.Title, e.Gender, r.Rate
FROM Person.Contact c 
INNER JOIN HumanResources.Employee e
ON c.ContactID = e.ContactID
INNER JOIN HumanResources.EmployeePayHistory r
ON r.EmployeeID = e.EmployeeID
ORDER BY Rate DESC;

See Also

Reference

SELECT (Transact-SQL)
INSERT (Transact-SQL)
UPDATE (Transact-SQL)
DELETE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History