MySQL LIMIT – MySQL Tutorial

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

mysql limit get highest values

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

mysql limit get lower values

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

MySQL LIMIT returns N rows

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 Out

MySQL LIMIT for pagination example

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

MySQL LIMIT for pagination example page 2

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

MySQL LIMIT find nth highest row check 1

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.

Contact US