SQL TOP / MySQL LIMIT clause

In this tutorial, you will learn how to retrieve a specified number of records from a table.

Limit result set

In some cases, you may not need to return all rows of the query, for example, if you only want to retrieve the most recently joined members of the organization.10employees, or get the top3students, etc.

To handle this situation, you can use the TOP clause in the SELECT statement in SQL. However, this TOP clause is only supported by SQL Server and MS Access database systems.

MySQL provides an equivalent LIMIT clause, while Oracle provides the ROWNUM clause for SELECT statements to limit the number of rows returned by the query.

SQL TOP syntax

The SQL TOP clause is used to limit the number of rows returned. Its basic syntax is:

SELECT TOP number | percent column_list FROM table_name;

Here,column_listis a list of database table column names or field names to be retrieved, separated by commas (for examplename,age,countryetc.). Let's see how it works.

Suppose we have aemployeesTable, which contains the following records:

+--------+--------------+------------+--------+---------+
|　emp_id　|　emp_name　　　　　|　hire_date　　|　salary　|　dept_id　|
+--------+--------------+------------+--------+---------+
|　　　　　　1　| Ethan Hunt |　2001-05-01　|　　　5000　|　　　　　　　4　|
|　　　　　　2　|　Tony　Montana　|　2002-07-15　|　　　6500　|　　　　　　　1　|
|　　　　　　3　|　Sarah　Connor　|　2005-10-18　|　　　8000　|　　　　　　　5　|
|　　　　　　4　|　Rick　Deckard　|　2007-01-03　|　　　7200　|　　　　　　　3　|
|　　　　　　5　| Martin Blank |　2008-06-24　|　　　5600 | NULL |
+--------+--------------+------------+--------+---------+

The following statement returnsemployees (employees)the top three highest-paid employees in the tableEmployees.

--　Syntax for SQL Server Database　　
SELECT TOP　3　*　FROM employees
ORDER BY salary DESC;

The result set returned is as follows:

+--------+--------------+------------+--------+---------+
|　emp_id　|　emp_name　　　　　|　hire_date　　|　salary　|　dept_id　|
+--------+--------------+------------+--------+---------+
|　　　　　　3　|　Sarah　Connor　|　2005-10-18　|　　　8000　|　　　　　　　5　|
|　　　　　　4　|　Rick　Deckard　|　2007-01-03　|　　　7200　|　　　　　　　3　|
|　　　　　　2　|　Tony　Montana　|　2002-07-15　|　　　6500　|　　　　　　　1　|
+--------+--------------+------------+--------+---------+

If you only want to retrieve the percentage of rows rather than a fixed number of rows, you can use the PERCENT keyword after the fixed value in the TOP clause. The decimal value is rounded up to the next integer value (for example,1.5rounded to2)。

The following statement returns the highest-paid30% of the employees.

--　SQL Server database syntax
SELECT TOP　30 PERCENT　*　FROM employees
ORDER BY salary DESC;

The result set returned by the above query will be as follows:

+--------+--------------+------------+--------+---------+
|　emp_id　|　emp_name　　　　　|　hire_date　　|　salary　|　dept_id　|
+--------+--------------+------------+--------+---------+
|　　　　　　3　|　Sarah　Connor　|　2005-10-18　|　　　8000　|　　　　　　　5　|
|　　　　　　4　|　Rick　Deckard　|　2007-01-03　|　　　7200　|　　　　　　　3　|
+--------+--------------+------------+--------+---------+

MySQL LIMIT syntax

MySQL's LIMIT clause has the same function as the SQL TOP clause. Its basic syntax is:

SELECT column_list FROM table_name LIMIT number;

The following statement returnsemployees (employees)the top three highest-paid employees in the tableEmployees.

--　MySQL database syntax
SELECT　*　FROM employees　
ORDER BY salary DESC LIMIT　3;

After execution, you will get the following output:

+--------+--------------+------------+--------+---------+
|　emp_id　|　emp_name　　　　　|　hire_date　　|　salary　|　dept_id　|
+--------+--------------+------------+--------+---------+
|　　　　　　3　|　Sarah　Connor　|　2005-10-18　|　　　8000　|　　　　　　　5　|
|　　　　　　4　|　Rick　Deckard　|　2007-01-03　|　　　7200　|　　　　　　　3　|
|　　　　　　2　|　Tony　Montana　|　2002-07-15　|　　　6500　|　　　　　　　1　|
+--------+--------------+------------+--------+---------+

Set the row offset in the LIMIT clause

The LIMIT clause accepts an optional second parameter.

When two parameters are specified, the first parameter specifies the offset of the first row to be returned, that is, the starting point, and the second parameter specifies the maximum number of rows to be returned. The initial offset of the row is 0 (not1)

Therefore, if you want to find the employee with the third highest salary, you can perform the following operation:

--　Syntax of MySQL Database
SELECT　*　FROM employees　
ORDER BY salary DESC LIMIT　2,　1;

After executing the above command, only one record will be obtained in the result set:

+--------+--------------+------------+--------+---------+
|　emp_id　|　emp_name　　　　　|　hire_date　　|　salary　|　dept_id　|
+--------+--------------+------------+--------+---------+
|　　　　　　2　|　Tony　Montana　|　2002-07-15　|　　　6500　|　　　　　　　1　|
+--------+--------------+------------+--------+---------+