The MySQL LIMIT and OFFSET Clauses [with Examples]

MySQL LIMIT and OFFSET Clauses

This tutorial covers limiting the number of results from a MySQL database query using the LIMIT clause and skipping results using the OFFSET clause.

This is especially useful when wanting to paginate results in web applications – spreading them out over several pages by retrieving a subset of the database results.

Examples

Examples will use the following table and data:

table_people

id name
1 Bob
2 Ted
3 Jim
4 Pip
5 Zak
6 Tim

LIMIT

Here’s how to limit the results to 2 records using LIMIT:

SELECT * FROM table_people LIMIT 2;

Which will return:

| id | name |
|----|------|
|  1 |  Bob |
|  2 |  Ted |

OFFSET

You can also start the results at a specific position – skipping a set number of records before including records in the results.

This example skips the first three results and returns 2 results total:

SELECT * FROM table_people LIMIT 2 OFFSET 3;

Which returns:

| id | name |
|----|------|
|  4 |  Pip |
|  5 |  Zak |

The OFFSET clause cannot be used independently of the LIMIT clause.

Combined LIMIT and OFFSET

The above query using both LIMIT and OFFSET can also be written as:

SELECT * FROM table_people LIMIT 3, 2;

This removes the need to type out OFFSET by including it in the LIMIT clause as the first parameter.

I prefer not to combine them, as having them displayed with both the words LIMIT and OFFSET makes it clearer what’s going on when skimming over your code.

LIMIT, OFFSET & Pagination

The most common usage for LIMIT and OFFSET is providing pagination.

This is pretty simple and works in any programming language:

  • LIMIT in this scenario is the number of records you want to display per page
  • OFFSET is where the records on the page start at
    • So, if you are on page 3, OFFSET should be 3 * LIMIT.
SHARE:
nv-author-image

Brad Morton

I'm Brad, and I'm nearing 20 years of experience with Linux. I've worked in just about every IT role there is before taking the leap into software development. Currently, I'm building desktop and web-based solutions with NodeJS and PHP hosted on Linux infrastructure. Visit my blog or find me on Twitter to see what I'm up to.

Leave a Reply

Your email address will not be published. Required fields are marked *