How to Limit Rows in a MySQL Result Set

Problem:

You’d like to limit the number of rows in a result set in MySQL.

Example:

Our database has a table named student with data in the columns id, first_name, last_name, and age.

Let’s select students’ full names and ages, but limit the rows returned to three.

Solution:

SELECT first_name, last_name, age
FROM student
LIMIT 3;

Here’s the result of the query:

Discussion:

The LIMIT clause limits the number of rows in the result set in MySQL database. It is used in the SELECT statement, usually at the end of the statement. (Even ORDER BY should be listed before LIMIT.)

The only required argument is the number of rows to display. In our example, LIMIT 3 produced a result set containing three rows. Unless you specify otherwise with the OFFSET argument, this function will always return the first n rows that meet query requirements.

LIMIT’s optional offset argument is placed before the argument indicating the number of returned rows. The offset indicates the position of the first row returned by LIMIT, i.e. 0 means the first row (no offset), 1 means the second row (offset of 1), etc. The offset and the number of rows are separated by a comma.

The query returns the same records as the previous query because the offset is zero. (Zero is the default offset value.)

SELECT first_name, last_name, age
FROM student
LIMIT 0, 3;

In these queries, the result rows are unordered. If you’d like to select three rows from a sorted result set, use ORDER BY:

SELECT first_name, last_name, age
FROM student
ORDER BY age DESC, last_name, first_name
LIMIT 1,3;

Here, we’re first ordering the rows by age (descending), then last name, then first name. We use the offset value to start on the second returned row and limit the results to three rows:

This query first sorts rows according to the age column in descending order. It then sorts by last_name and first_name in ascending order. If you look at the table, you’ll see that the oldest students (Steven, Lucy, Mary) are 25 years old. However, Mary is omitted because she is the first row and the offset is 1 (i.e. we’re starting with the second row). Lucy is now first because her first name comes before Steven’s. (Steven and Lucy have the same last name, so their order is determined by first name.) Michael is 22, which makes him the next-oldest student and last of the three rows returned.