Limiting the Number of Rows Returned

If you are expecting a query to still return more rows that you want, even with the filtering from a WHERE clause applied, you can add a LIMIT clause to specify the maximum number of records to be returned.

Using a LIMIT Clause

The following example retrieves all the rows from the customer_contacts table, but the LIMIT clause restricts the number of rows returned to three:

 mysql> SELECT first_name, last_name     -> FROM customer_contacts     -> ORDER BY last_name     -> LIMIT 3; +------------+-----------+ | first_name | last_name | +------------+-----------+ | Benjamin   | Britten   | | Marie      | Curie     | | Charles    | Darwin    | +------------+-----------+ 3 rows in set (0.00 sec) 

Limiting Sorted Records

The sorting specified in an ORDER BY clause is performed as if the full result of the query was fetched, regardless of the number of rows specified by the LIMIT clause.

By specifying sorting on a query column and using a LIMIT clause to limit the number of rows returned to just one, you can tell MySQL to return only the record with the highest or lowest value in that column. The following example returns only the most expensive product from the productstable, by sorting pricein descending order:

 mysql> SELECT *     -> FROM products     -> ORDER BY price DESC     -> LIMIT 1; +------+---------------+--------+-------+ | code | name          | weight | price | +------+---------------+--------+-------+ | MAXI | Large product |   8.00 | 15.99 | +------+---------------+--------+-------+ 1 row in set (0.00 sec) 

Skipping Rows

If the LIMIT clause contains two numbers separated by a comma, the first is an offset argument and the second is the number of rows to return. The offset specifies the number of rows to skip before returning thefirst record.

The next two queries show this in action. First select all the customers' email addresses in alphabetical order and show just the first three rows.

 mysql> SELECT email     -> FROM customer_contacts     -> ORDER BY email     -> LIMIT 3; +--------------------------+ | email                    | +--------------------------+ | [e-mail]       | | [e-mail]    | | [e-mail]   | +--------------------------+ 3 rows in set (0.00 sec) 

The next query uses an offset value to show the next three rows from the query result.

 mysql> SELECT email     -> FROM customer_contacts     -> ORDER BY email     -> LIMIT 3,3; +--------------------------+ | email                    | +--------------------------+ | [e-mail] | | [e-mail]    | | [e-mail] | +--------------------------+ 3 rows in set (0.00 sec) 

Not icethat there is no overlap herethe offset value of 3 causes the fourth, fifth, and sixth rows of the query result to be displayed.

Skipping Rows

Remember that the offset value in a LIMIT clause is the number of rows to skip, not the number of the first row to display. LIMIT 5 is equivalent to LIMIT 0,5not LIMIT 1,5.