TOP clause in T-SQL
Using TOP in T-SQL select given number of rows. It limits the rows returned in a query result set.
Let begin with TOP clause. Set AdventureWork2012
database up as current in SQL Server. You select all names of people and modified date from Person
table.
SELECT FirstName, LastName, ModifiedDate FROM Person.Person;
In result it is 19.972 rows. How select only 7 first rows? The TOP may do it.
SELECT TOP 7 FirstName, LastName, ModifiedDate FROM Person.Person;
Or
SELECT TOP (7) FirstName, LastName, ModifiedDate FROM Person.Person;
After TOP
it is correct to write only integer number or write integer number in brackets.
Through TOP
You may either select number of rows in percentage terms.
SELECT TOP 1 PERCENT FirstName, LastName, ModifiedDate FROM Person.Person;
This query select 200 rows. In percentage the number may be either in brackets.
SELECT TOP (1) PERCENT FirstName, LastName, ModifiedDate FROM Person.Person;
But if You would like present fraction of percent, You must use float number in brackets.
SELECT TOP (0.04) PERCENT FirstName, LastName, ModifiedDate FROM Person.Person;
The 0.04 percent of all rows is 8 rows.
After TOP the value is convert to bigint
or if PERCENT
is used to float
datatype.
If TOP is used with the ORDER BY
clause, the result set is limited to the first given after TOP
number of ordered rows. It is example to above query.
SELECT TOP (0.04) PERCENT FirstName, LastName, ModifiedDate FROM Person.Person ORDER BY LastName,FirstName;
The result with ORDER BY
clause:
In TOP with ORDER BY clause is used either WITH TIES expression. It denote that if You select only 3 ordered rows and last row and another several after them have identical data in columns given after ORDER BY clause, this additional rows will be display. It is example, if You select 3 last names from Person
table order by LastName
column:
SELECT TOP 3 LastName FROM Person.Person ORDER BY LastName;
The query displays 3 rows:
Using WITH TIES to TOP clause display 5 rows:
SELECT TOP 3 WITH TIES LastName FROM Person.Person ORDER BY LastName;
Using WITH TIES to TOP clause for several diffrent columns and only one column in ORDER BY, display either 5 rows:
SELECT TOP 3 WITH TIES FirstName, LastName, ModifiedDate FROM Person.Person ORDER BY LastName;
WITH TIES may be use only in SELECT statement and with ORDER BY clause. The TOP is used in SELECT, INSERT, UPDATE, DELETE, MERGE statement.