Articles Cookbook
Back to list
MySQL

How to Limit Rows in a MySQL Result Set

Database:

Operators:

LIMIT

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.

idfirst_namelast_nameage
1StevenWatson25
2LisaAnderson19
3AliceMiller19
4MaryBrown25
5LucyWatson25
6MichaelJackson22

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:

idfirst_namelast_nameage
1StevenWatson25
2LisaAnderson19
3AliceMiller19

Discussion:

The LIMIT clause limits the number of rows in the result set. 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. It indicates the position of the first row returned by LIMIT (i.e. ‘0’ is the first row, 1 is the second row, 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:

first_namelast_nameage
LucyWatson25
StevenWatson25
MichaelJackson22

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.

Recommended courses:

Recommended articles:

See also:

go to top