SQL SELECT TOP

The objective of this tutorial is to provide you the clear idea about SQL SELECT TOP statement with syntax and example.

The SQL SELECT TOP statement returns limited rows, specified in the TOP clause as a fixed value or percentage from the one or more tables. If a number say 5 is specified in the TOP clause then the query returns only 5 rows. If a percentage say 10% is specified then it returns only top 10% rows in the result set.

SQL SELECT TOP Syntax

The generic syntax for SQL SELECT TOP statement is as follows.

SELECT TOP (top_value) [ PERCENT ]
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.
• 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.
• ORDER BY expression – Optional. This is used to sort the selected rows in ascending or descending order. ASC is used to sort in ascending order and DESC is used to sort in descending order.

SQL SELECT TOP Example

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

1) SQL SELECT TOP – using TOP keyword

Let’s check an SQL example where TOP keyword will be used in the SELECT statement.

SELECT TOP(5) 
emp_id, emp_name, emp_gender
FROM Employee
WHERE emp_gender='M'
ORDER BY emp_no;

The above SQL query select the top 5 rows from the Employee table where gender of the employee is Male. If there is other records of Male employee in the Employee table, they will not be returned by the SELECT statement.

2) SQL SELECT TOP – using TOP Percentage keyword

Let’s check an SQL example where TOP  Percentage keyword will be used in the SELECT statement.

SELECT TOP(50) Percentage
emp_id, emp_name, emp_gender
FROM Employee
WHERE emp_gender='M'
ORDER BY emp_no;

The above SELECT statement returns top 10 percent records  from the full result set. In our example, it will return only 50 percent records from the Employee table where gender of the employee is Male. And other 50% records from the full result set would not be returned by the SQL Statement.

Summary: In this tutorial you have learned about how to user SQL SELECT TOP statement to get limited number of rows in the result set.