SQL Server SELECT TOP

Learning Objective

The objective of this tutorial is to teach you how to use the TOP clause in a SQL Server SELECT or UPDATE query.

Introduction to SQL Server Select TOP clause

The TOP clause takes the number of rows or a percentage of the query output as an argument. If a number say 5 is specified then the top 5 rows in the result set are displayed. If a percentage say 50% is specified then it returns 50% of the result set as the output. It can also be used to specify the number of rows to be updated in an UPDATE query.

SQL Server Select TOP Syntax

The generic syntax of SQL Server TOP syntax is as below.

SELECT TOP (top_value) [ PERCENT ] [ WITH TIES ]
expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

In this syntax,

• TOP (top_value) – Returns top number of rows from the result-set as specified in the top_value. For example, if you specify TOP(5), only the first 5 rows will be displayed.
• PERCENTAGE – Optional. If specified return rows in percent basis. For example, if you specify TOP(5) PERCENTAGE then only 5% rows from the result-set will be displayed.
• WITH TIES – Optional. If Specified, the rows tied in the last placed will be returned.
• expressions – The columns or calculations that you wish to retrieve.
• tables – Name of the tables from where data will be retrieved.
• WHERE conditions – Optional. The condition must be met to be part of the result-set.

SQL Server Select TOP Examples

We will use the below sample table for reference and example.

emp_id	emp_name	emp_phone	emp_gender	dept_code	emp_location
1270	Kalyan Purkayastha	9620139678	M	119	Kolkata
1271	Rajesh Pandey	9611895588	M	109	Bangalore
1272	Annie Bora	8884692570	F	121	Bangalore
1273	Dipankar Karmakar	9957889640	M	119	Kolkata
1274	Sunitha Rai	9742067708	F	109	Mumbai
1276	Parag Barman	8254066054	M	121	Kolkata
1277	Vinitha Sharma	9435746645	F	121	Mumbai
1278	Abhishek Saha	9850157207	M	109	Kolkata
1279	Rushang Desai	9850157207	M	109	Mumbai
1280	Arvin Kumar	8892340054	M	119	Bangalore

1) SQL Server Select TOP Example – using a number value

The following example will select only top 2 rows from the Employees table randomly. This is helpful data sample in a particular table.

SELECT TOP 2 * 
FROM employees;

Output

2) SQL Server Select TOP Example – using percentage value

The following example shows the usage of percentage value with SQL Server Select TOP clause. Here, we have specified 50% in TOP value. That means that the query will returns 50% of the result-set. In our case Employees table contains 10 rows, so it will return 5 rows.

SELECT TOP 50 PERCENT * 
FROM employee;

Output

3) SQL Server Select TOP Example – Using TOP with TIES

SQL Server TOP function along with TIES returns the rows which are matched the values in the last rows. Consider the below query to understand the same.

SELECT TOP 4 WITH TIES *
FROM employees
order by dept_code desc;

Output

In the above example, the fourth row has a dept_code of 119. As we have used TOP function with TIES, it returned two more employees having the same dept_code.

4) SQL Server Select TOP Example – using with update query

Please note that TOP does not take percentage in an UPDATE query. You have to specify the exact number of rows to update. The below query will update the top 4 rows of Employees table as ‘Kolkata’ in column emp_location.

UPDATE TOP (4) FROM employees 
SET emp_location=’Kolkata’;

Output