How to Use the LIMIT Clause in MySQL

08/04/2021

Contents

In this article, you will learn how to use the LIMIT clause in MySQL.

Using the LIMIT clause in MySQL

The LIMIT clause in MySQL is used to restrict the number of rows returned by a query. It is useful when dealing with large tables or when you want to paginate the results of a query.

Syntax

The syntax for using the LIMIT clause is as follows:

SELECT column1, column2, ...
FROM table_name
LIMIT [offset,] row_count;

The LIMIT clause accepts one or two arguments. The first argument specifies the offset of the first row to return, and the second argument specifies the maximum number of rows to return.

If you specify only one argument, MySQL will assume it is the maximum number of rows to return and start from the first row.

Examples

Let’s consider the following table employees:

+----+---------+--------+-----------+
| id | name    | gender | department|
+----+---------+--------+-----------+
|  1 | Alice   | Female | Sales     |
|  2 | Bob     | Male   | IT        |
|  3 | Charlie | Male   | HR        |
|  4 | David   | Male   | Sales     |
|  5 | Eve     | Female | IT        |
|  6 | Frank   | Male   | HR        |
+----+---------+--------+-----------+

Limiting the number of rows returned

Suppose we want to retrieve only the first three rows from the employees table. We can use the LIMIT clause to achieve this as shown below:

SELECT * FROM employees LIMIT 3;

The above query returns the following result:

+----+---------+--------+-----------+
| id | name    | gender | department|
+----+---------+--------+-----------+
|  1 | Alice   | Female | Sales     |
|  2 | Bob     | Male   | IT        |
|  3 | Charlie | Male   | HR        |
+----+---------+--------+-----------+

Using offset and row count

Suppose we want to retrieve the rows from employees table starting from the third row and limit the result to two rows. We can use the offset and row count values with the LIMIT clause to achieve this as shown below:

SELECT * FROM employees LIMIT 2 OFFSET 2;

The above query returns the following result:

+----+---------+--------+-----------+
| id | name    | gender | department|
+----+---------+--------+-----------+
|  3 | Charlie | Male   | HR        |
|  4 | David   | Male   | Sales     |
+----+---------+--------+-----------+

Using negative offset

Suppose we want to retrieve the last two rows from the employees table. We can use a negative offset value to achieve this as shown below:

SELECT * FROM employees ORDER BY id DESC LIMIT 2;

The above query returns the following result:

+----+-------+--------+-----------+
| id | name  | gender | department|
+----+-------+--------+-----------+
|  6 | Frank | Male   | HR        |
|  5 | Eve   | Female | IT        |
+----+-------+--------+-----------+

In the above query, we first sort the rows in descending order by the id column, then use a negative offset value to start from the end of the result set.

#MySQL