MySQL LIMIT Query – A Quick and Easy Guide

By /
Mysql Limit

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

MySQL 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 UPDATE or DELETE statements as well.

Syntax of MySQL LIMIT

SELECT expression FROM table_name LIMIT offset, count;Code language: SQL (Structured Query Language) (sql)

Where, 

  • 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.

Employee Table Limit
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,

Limit With Single Argument

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,

Limit With Offset And Count

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,

Limit Where Clause

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 Order By

Conclusion

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

References