In this tutorial, we will learn the MySQL
LIMIT clause. Suppose you want to display the list of students in a table, in descending order of marks in Maths. That can be done using the
WHERE clause. However, if you only want to display the top three students who scored the highest in Maths, MySQL provides you with the
LIMIT is used to restrict the number of rows that are returned in a result-set.
LIMIT is widely used along with the
SELECT statement but can be used along with the
DELETE statements as well.
Syntax of MySQL LIMIT
SELECT expression FROM table_name LIMIT offset, count;Code language: SQL (Structured Query Language) (sql)
- Offset is used to specify the number of a row from which you want to return the result-set. The offset of the row starts from 0. For example, if you set the offset as 2, the result-set is returned from row 3 onwards.
- The count is used to specify the maximum number of rows you want to return. For instance, if you want to return the first 3 rows, then you set the count to 3.
Note that, if you only specify one argument out of the two, MySQL assumes that you have specified the count argument.
Example of MySQL LIMIT
Consider the following Employee table.
1. MySQL LIMIT with Single Argument
In the syntax section, I mentioned that if you specify only a single argument with
LIMIT, MySQL assumes it to be the count argument. So keeping that in mind, let us display the first 5 employees at our table.
SELECT * FROM Employee LIMIT 5;Code language: SQL (Structured Query Language) (sql)
MySQL treats the value 5 as the count value and we get the output as,
2. MySQL LIMIT with Both Arguments
Now, let us specify an offset along with our
LIMIT clause. Suppose, you want to know all the employees who have their records from the 4th row till the 7th row in the table. We do so using,
SELECT * FROM Employee LIMIT 3, 4;Code language: SQL (Structured Query Language) (sql)
Since offset value starts at 0, we need to specify offset as 3 to retrieve the 4th row. We need records from the 4th row till and including the 7th row and hence we put our count value as 4. We get the output as,
3. MySQL LIMIT with MySQL WHERE
Let us use the
WHERE clause along with the
LIMIT clause. How about displaying the first three entries in the table where the employee has a salary greater than 75000. We do so using the query,
SELECT * FROM Employee WHERE Salary>75000 LIMIT 3;Code language: SQL (Structured Query Language) (sql)
And we get the output as follows,
4. MySQL LIMIT Query with MySQL ORDER BY
What if you want to see the top 5 highest-paid employees on your table? In such a case, we use the ORDER BY clause along with the
LIMIT clause as follows:
SELECT * FROM Employee ORDER BY Salary DESC LIMIT 5;Code language: SQL (Structured Query Language) (sql)
This will order the Salary column in descending order and then display the first 5 entries. The output is shown below.
LIMIT clause can be very useful in the analysis of data in your tables. Suppose you have to display only 20 entries per page from your table onto a web page. That is precisely where you will find the need for using the
- MySQL official documentation on
- JournalDev article on