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.
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.
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.
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 TOP (2) INTO Table2 (ColumnB) SELECT ColumnA FROM Table1 ORDER BY ColumnA
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.
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
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;