What is the MySQL LIMIT Clause?
The LIMIT
clause is used to specify the number of records to return. The LIMIT
clause is useful on large tables with thousands of records. Returning a large number of records can impact performance. The LIMIT
clause is used in the SELECT
statement to constrain the number of rows to return. The LIMIT
clause accepts one or two arguments. The values of both arguments must be zero or positive integers.
It is essential in such a case when the table contains thousands of rows, or you want to return only the recently inserted data. In other words, if you are not interested in getting all the rows returned from the query, use the MySQL Limit clause with the SELECT statement. It improves the performance of the query and even stops having crashed the system when the table contains a large number of data.
LIMIT Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
- The
offset
specifies the offset of the first row to return. Theoffset
of the first row is 0, not 1. - The
row_count
specifies the maximum number of rows to return.
The following picture illustrates the LIMIT
clause:

When you use the LIMIT
clause with one argument, MySQL will use this argument to determine the maximum number of rows to return from the first row of the result set. Therefore, these two clauses are equivalent:
LIMIT row_count;
Code language: SQL (Structured Query Language) (sql)
And
LIMIT 0 , row_count;
In addition to the above syntax, MySQL provides the following alternative LIMIT
clause syntax:
LIMIT row_count OFFSET offset
How do I LIMIT MySQL results?
Limit Data Selections From a MySQL Database .Assume we wish to select all records from 1 – 30 (inclusive) from a table called “Orders”. The SQL query would then look like this: $sql = “SELECT * FROM Orders LIMIT 30”; When the SQL query above is run, it will return the first 30 records.
Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
MySQL LIMIT Examples
The following SQL statement selects the first three records from the “Customers” table:
Example
SELECT * FROM Customers LIMIT 3;
ADD a c
The following SQL statement selects the first three records from the “Customers” table, where the country is “Germany”:
Example
SELECT ;FROM;Customers<br>WHERE;Country='Germany'<br>LIMIT;3;
MySQL LIMIT clause examples
We’ll use the customers
table from the sample database for demonstration.

1) Using MySQL LIMIT to get the highest or lowest rows
This statement uses the LIMIT
clause to get the top five customers who have the highest credit:
<code>SELECT customerNumber, customerName, creditLimit FROM customers ORDER BY creditLimit DESC LIMIT 5;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

In this example:
- First, the
ORDER BY
clause sorts the customers by credits in high to low. - Then, the
LIMIT
clause returns the first 5 rows.
Similarly, this example uses the LIMIT
clause to find five customers who have the lowest credits:
<code>SELECT customerNumber, customerName, creditLimit FROM customers ORDER BY creditLimit LIMIT 5;</code><small>Code language: SQL (Structured Query Language) (sql)</small

In this example:
- First, the
ORDER BY
clause sorts the customers by credits in low to high. - Then, the
LIMIT
clause returns the first 5 rows.
Because there are more than 5 customers that have credits zero, the result of the query above may lead to an inconsistent result.
To fix this issue, you need to add more columns to the ORDER BY
clause to constrain the row in unique order:
<code>SELECT customerNumber, customerName, creditLimit FROM customers ORDER BY creditLimit, customerNumber LIMIT 5;</code><small>Code language: SQL (Structured Query Language) (sql)</small

2) Using MySQL LIMIT clause for pagination
When you display data on the screen, you often want to divide rows into pages, where each page contains a limited number of rows like 10 or 20.
To calculate the number of pages, you take the total rows divided by the number of rows per page. For fetching rows of a specific page, you can use the LIMIT
clause.
This query uses the COUNT(*)
aggregate function to get the total rows from the customers
table:
<code>SELECT COUNT(*) FROM customers;</code><small>Code language: SQL (Structured Query Language) (sql)</small>
+----------+
| COUNT(*) |
+----------+
| 122 |
+----------+
1 row in set (0.00 sec)
Code language: JavaScript (javascript)
Suppose that each page has 10 rows; to display 122 customers, you have 13 pages. The last 13th page contains two rows only.
This query uses the LIMIT
clause to get rows of page 1 which contains the first 10 customers sorted by the customer name:
<code>SELECT customerNumber, customerName FROM customers ORDER BY customerName LIMIT 10;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

This query uses the LIMIT
clause to get the rows of the second page that include rows 11 – 20:
<code>SELECT customerNumber, customerName FROM customers ORDER BY customerName LIMIT 10, 10;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

In this example, the clause LIMIT 10, 10
returns 10 rows for the row 11 – 20.
3) Using MySQL LIMIT to get the nth highest or lowest value
To get the nth highest or lowest value, you use the following LIMIT
clause:
<code>SELECT select_list FROM table_name ORDER BY sort_expression LIMIT n-1, 1;</code><small>Code language: SQL (Structured Query Language) (sql)</small>
The clause LIMIT n-1, 1
returns 1
row starting at the row n
. For example, the following finds the customer who has the second-highest credit:
<code>SELECT customerName, creditLimit FROM customers ORDER BY creditLimit DESC LIMIT 1,1;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Let’s double-check the result. This query returns all customers sorted by credits from high to low:
<code>SELECT customerName, creditLimit FROM customers ORDER BY creditLimit DESC;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

As you can see clearly from the output, the result was correct as expected. Note that this technique works when there are no two customers who have the same credit limits. To get a more accurate result, you should use the DENSE_RANK()
window function.
MySQL LIMIT & DISTINCT clauses
If you use the LIMIT
clause with the DISTINCT
clause, MySQL immediately stops searching when it finds the number of unique rows specified in the LIMIT
clause. The example uses the LIMIT
clause with the DISTINCT
clause to return the first five unique states in the customers
table:
<code>SELECT DISTINCT state FROM customers WHERE state IS NOT NULL LIMIT 5;</code><small>Code language: SQL (Structured Query Language) (sql)</small>
