Summary: In this tutorial, you will learn how to use the MySQL LIMIT clause to restrict the number of rows returned by a query.
Introduction
to the MySQL LIMIT Clause
The LIMIT clause is used in the SELECT statement to restrict the number of rows to be returned. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero integers or positive.
The following illustrates the syntax of the LIMIT clause with two arguments:
SELECT select_list OF table_name BOUNDARY [OFFSET,] row_count; Code language: Structured Query Language (SQL)
In this syntax:
- Offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
- The row_count specifies the maximum number of rows to return.
The following image illustrates
the LIMIT clause:When you use the LIMIT
clause with an 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 preceding syntax,
MySQL provides the following alternative LIMIT clause syntax
: LIMIT row_count OFFSET offsetCode language: SQL (Structured Query Language) (sql)
The LIMIT and ORDER BY clauses
By default, the SELECT statement returns rows in an unspecified order. When you add the LIMIT clause to the SELECT statement, the rows returned are unpredictable.
Therefore, to ensure that the LIMIT clause returns an expected output, you should always use it with an ORDER BY clause like this:
SELECT select_list OF table_name ORDER BY sort_expression COMPENSATED LIMIT, row_count; Code language: SQL (Structured Query Language) (sql)
The following image illustrates the evaluation order of
the LIMIT clause in the SELECT statement:MySQL LIMIT
Clause Examples
We will use the sample database customer table for the demonstration.
1) Using MySQL LIMIT to get the highest or lowest ranks
This statement uses the LIMIT clause to get the top five customers who have the credit highest:
SELECT customerNumber, customerName, creditLimit FROM clients ORDER BY creditLimit DESC LIMIT 5; Code language: SQL (Structured Query Language) (sql)
Try It Out
In this example
- 😛 rimero, the ORDER BY clause sorts customers by credits from highest to lowest
- The LIMIT clause then returns the first 5 rows.
.
Similarly, this example uses the LIMIT clause to find five customers who have the lowest credits:
SELECT customerNumber, customerName, creditLimit FROM clients ORDER BY creditLimit LIMIT 5; Code language: SQL (Structured Query Language) (sql)
Try It Out
In this example
- 😛 rimer, the ORDER BY clause sorts customers by credits from lowest to highest
- The LIMIT clause then returns the first 5 rows.
.
Because
there are more than 5 customers who have zero credits, the result of the previous query may lead to an inconsistent result.
To work around this issue, you must add more columns to the ORDER BY clause to constrain the row in a single order:
SELECT customerNumber, customerName, creditLimit FROM clients ORDER BY creditLimit, customerNumber LIMIT 5; Code language: SQL (Structured Query Language) (sql)
Try it
2) Using the MySQL LIMIT clause for paging
When you display data on the screen, you often want to divide rows into pages, where each page contains a limited number of rows such as 10 or 20.
To calculate the number of
pages, take the total rows divided by the number of rows per page. To get rows from a specific page, you can use the LIMIT clause.
This query uses the COUNT(*) aggregate function to get the total rows in the customers table:
SELECT COUNT(*) FROM customers; Code language: SQL (Structured Query Language) (sql)+-+ | COUNT(*) | +-+ | 122 | +-+ 1 row altogether (0.00 sec)Code language: JavaScript (javascript)
Suppose each page has 10 rows; to display 122 clients, it has 13 pages. The last page 13 contains only two rows.
This query uses the LIMIT clause to get rows on page 1 that contains the first 10 customers sorted by customer name:
SELECT customerNumber, customerName FROM clients ORDER BY customerName LIMIT 10; Code language: SQL (Structured Query Language) (sql)
Try it
This query uses the LIMIT clause to get the rows on the second page that include rows 11 – 20:
SELECT customerNumber, customerName FROM clients ORDER BY customerName LIMIT 10, 10; Code language: SQL (Structured Query Language) (sql)
Try It Out
In this example, the LIMIT clause 10, 10 returns 10
rows for row 11 – 20.
3) Using MySQL LIMIT to get the nth highest or lowest value To get the nth highest or lowest value
,
use the following LIMIT clause:
SELECT select_list OF table_name ORDER BY sort_expression LIMIT n-1, 1; Code language: SQL (Structured Query Language) (sql)
The LIMIT clause n-1, 1 returns 1 row starting in row n
.
For example, below is the customer who has the second highest credit: SELECT customerName,
creditLimit FROM clients ORDER BY creditLimit DESC LIMIT 1.1; Code language: SQL (Structured Query Language) (sql)
Try it Let’s
check the result. This query returns all customers sorted by credits from highest to lowest:
SELECT customerName, creditLimit FROM clients ORDER BY creditLimit DESC; Code language: SQL (Structured Query Language) (sql)
Try It Out
As you can clearly see from the output, the result was correct as expected
.
Note that this technique works when no two customers have the same credit limits. For 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 with the DISTINCT clause
, it 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:
SELECT THE STATE OTHER THAN CLIENTS WHERE THE STATUS IS NOT NULL LIMIT 5; Code language: SQL (Structured Query Language) (sql)
Try it
Summary
- Use the MySQL LIMIT clause to restrict the number of rows returned by the SELECT statement.
