SQL TOP / MySQL LIMIT Clause

In this tutorial you will learn how to retrieve fixed number of records from the table.

Limiting Result Sets

In some situations, you may not be interested in all of the rows returned by a query, for example, if you just want to retrieve the top 10 employees who recently joined the organization, get top 3 students by score, or something like that.

To handle such situations, you can use SQL’s TOP clause in your SELECT statement. However the TOP clause is only supported by the SQL Server and MS Access database systems.

MySQL provides an equivalent LIMIT clause, whereas Oracle provides ROWNUM clause for the SELECT statement to restrict the number of rows returned by a 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_list is a comma separated list of column or field names of a database table (e.g. name, age, country, etc.) whose values you want to fetch. Let’s see how it works.

Suppose we’ve an employees table in our database with 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 returns top three highest-paid employees from the employees table.

Example

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

The result set returned will look something like this:

+--------+--------------+------------+--------+---------+
| 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 |
+--------+--------------+------------+--------+---------+

You can optionally use the PERCENT keyword after the fixed value in a TOP clause, if you just want to retrieve the percentage of rows instead of fixed number of rows. Fractional values are rounded up to the next integer value (e.g. 1.5 rounded to 2).

The following statement returns top 30 percent of the highest-paid employees.

Example

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

The result set returned by the above query will look like this:

+--------+--------------+------------+--------+---------+
| 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

The MySQL’s LIMIT clause does the same work as SQL TOP clause. Its basic syntax is:

SELECT `column_list` FROM `table_name` LIMIT *number*;

The following statement returns top three highest-paid employees from the employees table.

Example

-- Syntax for MySQL Database 
SELECT * FROM employees 
ORDER BY salary DESC LIMIT 3;

After execution, you’ll get the output something like this:

+--------+--------------+------------+--------+---------+
| 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 |
+--------+--------------+------------+--------+---------+

Note: In a SELECT statement, always use an ORDER BY clause with the LIMIT clause. Otherwise, you may not get the desired result.

Setting Row Offset in 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 return i.e. the starting point, whereas the second parameter specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).

So, if you want to find out the third-highest paid employee, you can do the following:

Example

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

After executing the above command, you’ll get only one record in your result set:

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