MySQL LIMIT

 MySQL LIMIT

Summary: in this tutorial, you will learn how to use the MySQL LIMIT clause to constrain the number of rows returned by a query.

Introduction to MySQL LIMIT clause

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

The following illustrates the LIMIT clause syntax with two arguments:

SELECT 
    select_list
FROM
    table_name
LIMIT [offset,] row_count;

In this syntax:

• The 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 picture illustrates the LIMIT clause:

When you use the LIMIT the 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;

And

LIMIT 0 , row_count;

In addition to the above syntax, MySQL provides the following alternative LIMIT clause for compatibility with PostgreSQL.

LIMIT row_count OFFSET offset

LIMIT and ORDER BY clauses

The SELECT statement without an ORDER BY clause returns rows in an unspecified order. It means that rows can be in any order. When you apply the LIMIT clause to this unordered result set,  you will not know which rows the query will return.

For example, you may want to get fifth through tenth rows, but fifth through tenth in what order? The order of rows is unknown unless you specify the ORDER BY clause.

Therefore, it is a good practice to always use the LIMIT clause with the ORDER BY clause to constraint the result rows in a unique order.

SELECT select_list
FROM table_name
ORDER BY order_expression
LIMIT offset, row_count;

The following picture illustrates the evaluation order of the LIMIT clause in the SELECT statement:

MySQL LIMIT 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:

SELECT 
    customerNumber, 
    customerName, 
    creditLimit
FROM
    customers
ORDER BY creditLimit DESC
LIMIT 5;

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 5 customers who have the lowest credits:

SELECT 
    customerNumber, 
    customerName, 
    creditLimit
FROM
    customers
ORDER BY creditLimit
LIMIT 5;

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:

SELECT 
    customerNumber, 
    customerName, 
    creditLimit
FROM
    customers
ORDER BY 
    creditLimit, 
    customerNumber
LIMIT 5;

2) Using MySQL LIMIT for pagination

When you display data on applications, you often want to divide rows into pages, where each page contains a certain number of rows like 5, 10, or 20.

To calculate the number of pages, you get 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:

SELECT COUNT(*) FROM customers;

+----------+
| COUNT(*) |
+----------+
|      122 |
+----------+
1 row in set (0.00 sec)

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:

SELECT 
    customerNumber, 
    customerName
FROM
    customers
ORDER BY customerName    
LIMIT 10;

This query uses the LIMIT clause to get the rows of the second page that include rows 11 – 20:

SELECT 
    customerNumber, 
    customerName
FROM
    customers
ORDER BY customerName    
LIMIT 10, 10;

In this example, the clause LIMIT 10, 10 returns 10 rows for the rows 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:

SELECT select_list
FROM table_name
ORDER BY sort_expression
LIMIT n-1, 1;

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:

SELECT 
    customerName, 
    creditLimit
FROM
    customers
ORDER BY 
    creditLimit DESC    
LIMIT 1,1;

Let’s double-check the result. This query returns all customers sorted by credits from high to low:

SELECT 
    customerName, 
    creditLimit
FROM
    customers
ORDER BY 
    creditLimit DESC;

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.

In this tutorial, you have learned how to use MySQL LIMIT clause to constrain the number of rows returned by the SELECT statement.