Home / SQL Statements / Select Statement / Question No: 216

MySQL supports a LIMIT clause in SELECT, UPDATE, DELETE statements. In SELECT, the LIMIT clause tells the server to return only some of the rows selected by the statement. This is useful for retrieving records based on their position within the set of selected rows. LIMIT may be given with either one or two arguments:

LIMIT row_count
LIMIT skip_count, row_count

Each argument must be given as an integer constant. You cannot use expressions, user variables, and so forth. When followed by a single integer, row_count, LIMIT returns the first row_count rows from the beginning of the result set. To select just the first 10 rows of a result set, use LIMIT 10:

SELECT * FROM Country LIMIT 10;

When followed by two integers, skip_count and row_count, LIMIT skips the first skip_count rows from the beginning of the result set, and then returns the next row_count rows. To skip the first 20 rows and then return the next 10 rows, do this:

SELECT * FROM Country LIMIT 20,10;

The single-argument form of LIMIT is applicable only when the rows you want to retrieve appear at the beginning of the result set. The two-argument form is more general and can be used to select an arbitrary section of rows from anywhere in the result set.

When you need only some of the rows selected by a query, LIMIT is an efficient way to obtain them. For a client application that fetches rows from the server, you get better performance by adding LIMIT to the query than by having the client fetch all the rows and discard all but the ones of interest. By using LIMIT, the unwanted rows never cross the network at all.

It's often helpful to include an ORDER BY clause to put the rows in a particular order when you use LIMIT. When ORDER BY and LIMIT are used together, MySQL applies ORDER BY first and then LIMIT. One common use for this is to find the row containing the smallest or largest values in a particular column. For example, to find the row in a table t containing the smallest id value, use this statement:

SELECT * FROM t ORDER BY id LIMIT 1;

To find the largest value instead, use DESC to sort the rows in reverse:

SELECT * FROM t ORDER BY id DESC LIMIT 1;

The two-argument form of LIMIT is useful in conjunction with ORDER BY for situations in which you want to process successive sections of a result set. For example, in Web applications, it's common to display the result of a large search across a series of pages that each present one section of the result. To retrieve sections of the search result this way, issue a series of statements that all specify the same number of rows to return in the LIMIT clause, but vary the number of initial rows to skip:

SELECT * FROM t ORDER BY id LIMIT  0, 20;
SELECT * FROM t ORDER BY id LIMIT 20, 20;
SELECT * FROM t ORDER BY id LIMIT 40, 20;
SELECT * FROM t ORDER BY id LIMIT 60, 20;

It's possible to abuse the LIMIT feature. For example, it isn't a good idea to use a clause such as LIMIT 1000000, 10 to return 10 rows from a query that normally would return more than a million rows. The server must still process the query to determine the first million rows before returning the 10 rows. It's better to use a WHERE clause to reduce the query result to a more manageable size, and then use LIMIT to pull rows from that reduced result. This also makes the use of ORDER BY with LIMIT more efficient because the server need not sort as large a row set before applying the limit.

In UPDATE and DELETE statements, the LIMIT clause causes only a certain number of rows to be updated or deleted.