SQL LIMIT Clause

SQL LIMIT 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 LIMIT clause is supported by the MySQL database system.
  • An equivalent clause TOP is supported by the SQL Server and MS Access database systems.
  • 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 LIMIT clause.

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

Syntax

Here is the basic syntax for the LIMIT 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 number represents the limit on the number of records in the result set.
  • The offset_value is an optional parameter, that defines the position of the first record in the result set from where the records are to be pulled, which starts with zero 0 for the first record.
SELECT column_list FROM table_name LIMIT number;

In the case of the offset value, the syntax looks as below.

SELECT column_list FROM table_name LIMIT offset_value, number;

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

LIMIT Number

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

Run this on IDE

SELECT * FROM employees 
ORDER BY salary DESC LIMIT 3;

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 * FROM employees 
ORDER BY hire_date DESC LIMIT 3;

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

LIMIT With Offset

The below SQL statement returns the three highest-paid employees, starting from the 2nd row.

  • The offset value for the 2nd row is 1, from where the 3 records must be returned.

Run this on IDE

SELECT * FROM employees 
ORDER BY salary DESC LIMIT 1, 3;

After successful execution, the output looks like this, which contains the three highest-paid employees, starting from the 2nd row as the offset is 1.

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

Similarly, the below SQL statement returns the three recently hired employees, starting from the 2nd row.

SELECT * FROM employees 
ORDER BY hire_date DESC LIMIT 1, 3;

After successful execution, the output looks like this, which contains the three recently hired employees, starting from the 2nd row as the offset is 1.

employee_id employee_name gender birth_date hire_date salary
4 James Cooper M 2000-10-20 2015-05-10 45000
3 Andrew Russel M 1998-05-04 2012-02-20 28000
1 Robin Hood M 1990-10-10 2010-10-15 25000

Other Statements

The LIMIT 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 LIMIT clause.

Related Links