The 'TOP' clause is used to get the top records in a query. It is
used to retrieve a single/multiple record(s) from a table based on the number you give.
Here the 'Top' clause is executed on the 'Employees' table.
The TOP Clause
The 'TOP' clause helps you retrieve data from a table. The Top clause is used
against a particular column. The retrieval of records will be based on that
column and by default it will be ascending. If you want to retrieve the records
by descending order you will need to add the desc in the Order By clause.
Normally it is used to filter out data from tables with large data and bring out a subset of it.
While bringing out a large amount of data, it can impact the performance of the server.
SQL 'TOP' Clause Syntax
Select TOP n column-3,column-1,column-2,... column-n
from table
order by column-3 desc
The 'Employees' tables contains the following columns and their respective data as shown below.
1 |
Richard Hughes |
4/23/1945 12:00:00 AM |
1 |
1 |
New Orleans |
2 |
Taryn Sinclair |
3/22/1980 12:00:00 AM |
2 |
2 |
San Francisco |
3 |
Ted Horowitz |
3/31/1960 12:00:00 AM |
3 |
4 |
New York |
4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
3 |
4 |
Salt Lake City |
5 |
Miranda Leigh |
7/19/1983 12:00:00 AM |
7 |
3 |
New York |
6 |
Jana Rae |
9/23/1976 12:00:00 AM |
8 |
2 |
Houston |
7 |
Lita Rosanna |
9/14/1982 12:00:00 AM |
4 |
6 |
Long Island |
8 |
Colin Flooks |
12/29/1988 12:00:00 AM |
4 |
2 |
Salt Lake City |
9 |
Anthony Frank |
2/22/1988 12:00:00 AM |
9 |
3 |
San Francisco |
10 |
Stephanie Lynn |
7/30/1979 12:00:00 AM |
8 |
2 |
Salt Lake City |
11 |
Jack Martin |
8/25/1985 12:00:00 AM |
NULL |
NULL |
Austin |
The Top Clause Example
To retrieve the 'Top' 7 salaries and arrange it from the max in a descending order from the 'Employees' table to
get the max value at the top.
Select Top 7 a.EmployeeID,a.EmployeeName,a.DateOfBirth,b.Designation, c.DeptName, salary
from Employees a, Designation b, Department c
where a.DeptID=c.DeptID and a.DesignationId=b.DesignationID order by Salary desc
1 |
Richard Hughes |
4/23/1945 12:00:00 AM |
CEO |
Finance |
950.00 |
2 |
Taryn Sinclair |
3/22/1980 12:00:00 AM |
CTO |
Development |
800.00 |
9 |
Anthony Frank |
2/22/1988 12:00:00 AM |
Trainee Engineer |
Testing |
750.00 |
3 |
Ted Horowitz |
3/31/1960 12:00:00 AM |
Program Manager |
Delivery |
700.00 |
7 |
Lita Rosanna |
9/14/1982 12:00:00 AM |
Project Lead |
Administration |
650.00 |
8 |
Colin Flooks |
12/29/1988 12:00:00 AM |
Project Lead |
Development |
600.00 |
4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
Program Manager |
Delivery |
600.00 |