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_idemp_nameemp_phoneemp_genderdept_codeemp_location
1270Kalyan Purkayastha9620139678M119Kolkata
1271Rajesh Pandey9611895588M109Bangalore
1272Annie Bora8884692570F121Bangalore
1273Dipankar Karmakar9957889640M119Kolkata
1274Sunitha Rai9742067708F109Mumbai
1276Parag Barman8254066054M121Kolkata
1277Vinitha Sharma9435746645F121Mumbai
1278Abhishek Saha9850157207M109Kolkata
1279Rushang Desai9850157207M109Mumbai
1280Arvin Kumar8892340054M119Bangalore
Employees (Table containing data of employees)

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

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

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

Sql Server Top example

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

SQL Server TOP with TIES

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

sql server top example

Advertisement