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.

emp_id emp_name emp_gender dept_code emp_location
1510 Avinash Sirsath M 101 Mumbai
1511 Ramjan Ali M 105 Kolkata
1512 Piyanka Saha F 103 Kolkata
1513 Piyam mondal M 101 Kolkata
1514 Jitu Garg M 104 Mumbai
1515 Raman Sahani M 105 Delhi
1516 Lata Malhotra F 104 Mumbai
1517 Prakash Javedkar M 101 Delhi
1518 Nitu Rani F 103 Mumbai
1519 Pallab Das M 103 Kolkata
1520 Pankaj Sharma M 101 Bangalore

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.

emp_id emp_name emp_gender
1510 Avinash Sirsath M
1511 Ramjan Ali M
1513 Piyam mondal M
1514 Jitu Garg M
1515 Raman Sahani M

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.

emp_id emp_name emp_gender
1510 Avinash Sirsath M
1511 Ramjan Ali M
1513 Piyam mondal M
1514 Jitu Garg M

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.