SQL TOP Clause

SQL TOP clause can be used to limit the rows returned by a SELECT statement when we are not interested in rows more than a certain limit.

  • The TOP clause is supported by the SQL Server and MS Access database systems.
  • An equivalent clause LIMIT is supported by the MySQL database system.
  • An equivalent clause ROWNUM is supported by the Oracle database system.

In the previous chapters, we have seen how to sort records returned by a SELECT statement using the ORDER BY clause.

Now, let's see how to limit the records returned by a SELECT statement using the TOP clause.

NOTE: Always use the TOP clause along with the ORDER BY clause on a SELECT statement for desired results.

Syntax

Here is the basic syntax for the TOP clause on a SELECT statement.

  • The column_list is a comma-separated list of table columns.
  • The table_name is the name of the table.
  • The TOP clause must be followed by a number or a percentage followed by a PERCENT keyword.
SELECT TOP number | percent column_list FROM table_name;

Table Data

Let's consider the below data in the table employees before executing any of the below queries.

employee_id employee_name gender birth_date hire_date salary
1 Robin Hood M 1990-10-10 2010-10-15 25000
2 Tony Blank M 1982-08-07 2010-01-05 89000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
4 James Cooper M 2000-10-20 2015-05-10 45000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

TOP Number

The below SQL statement returns the top three highest-paid employees.

Run this on IDE

SELECT TOP 3 * FROM employees 
ORDER BY salary DESC;

After successful execution, the output looks like this, which contains the three highest-paid employees.

employee_id employee_name gender birth_date hire_date salary
2 Tony Blank M 1982-08-07 2010-01-05 89000
5 Rose Cutler F 1985-08-08 2015-06-21 65000
4 James Cooper M 2000-10-20 2015-05-10 45000

Similarly, the below SQL statement returns the top three recently hired employees.

SELECT TOP 3 * FROM employees 
ORDER BY hire_date DESC;

After successful execution, the output looks like this, which contains the three recently hired employees.

employee_id employee_name gender birth_date hire_date salary
5 Rose Cutler F 1985-08-08 2015-06-21 65000
4 James Cooper M 2000-10-20 2015-05-10 45000
3 Andrew Russel M 1998-05-04 2012-02-20 28000

TOP Percentage

The below SQL statement returns the top 20% of the total records from the highest-paid employees.

  • Here 20% of the total 5 records means 2 records.

Run this on IDE

SELECT TOP 20 PERCENT * FROM employees 
ORDER BY salary DESC;

After successful execution, the output looks like this, which contains the top two highest-paid employees, which constitute 20% of the records.

employee_id employee_name gender birth_date hire_date salary
2 Tony Blank M 1982-08-07 2010-01-05 89000
5 Rose Cutler F 1985-08-08 2015-06-21 65000

Similarly, the below SQL statement returns the top three recently hired employees.

SELECT TOP 20 PERCENT * FROM employees 
ORDER BY hire_date DESC;

After successful execution, the output looks like this, which contains the top two recently hired employees, which constitute 20% of the records.

employee_id employee_name gender birth_date hire_date salary
5 Rose Cutler F 1985-08-08 2015-06-21 65000
4 James Cooper M 2000-10-20 2015-05-10 45000

Other Statements

The TOP clause can only be applied to a SELECT statement.

Overall

We now know how to limit the records in a result set of a SELECT statement using the TOP clause.

Related Links