MySQL LIMIT

The LIMIT clause restricts the number of rows returned by a query. Instead of retrieving thousands of records at once, LIMIT allows fetching only as many rows as needed. This is especially useful for pagination, performance, and previewing data.

Syntax

SELECT column1, column2
FROM table_name
LIMIT number;

number specifies the maximum number of rows to return.

Sample Table: products

+------------+--------------+-------+-------+
| product_id | product_name | price | stock |
+------------+--------------+-------+-------+
|          1 | Notebook     | 12.99 |   200 |
|          2 | Pen          |  1.99 |   500 |
|          3 | Eraser       |  0.99 |   800 |
|          4 | Ruler        |  3.49 |   300 |
|          5 | Calculator   | 24.99 |   150 |
|          6 | Stapler      | 15.00 |    80 |
+------------+--------------+-------+-------+

Basic Example: Limiting to 3 Rows

SELECT * FROM products
LIMIT 3;

Output:

+------------+--------------+-------+-------+
| product_id | product_name | price | stock |
+------------+--------------+-------+-------+
|          1 | Notebook     | 12.99 |   200 |
|          2 | Pen          |  1.99 |   500 |
|          3 | Eraser       |  0.99 |   800 |
+------------+--------------+-------+-------+

Only the first 3 rows are returned.

LIMIT with ORDER BY

Combining LIMIT with ORDER BY allows for meaningful selections — for example, finding the top 3 most expensive products:

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 3;

Output:

+--------------+-------+
| product_name | price |
+--------------+-------+
| Calculator   | 24.99 |
| Stapler      | 15.00 |
| Notebook     | 12.99 |
+--------------+-------+

LIMIT with OFFSET

The OFFSET keyword (or a second number in LIMIT) skips a specified number of rows before returning results. This is the foundation of pagination.

Syntax

SELECT column1
FROM table_name
LIMIT row_count OFFSET skip_count;

Or the shorthand version:

LIMIT skip_count, row_count

Example: Skip the first 2, return the next 3

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 3 OFFSET 2;

This skips the 2 most expensive products and returns the next 3.

Output:

+--------------+-------+
| product_name | price |
+--------------+-------+
| Notebook     | 12.99 |
| Ruler        |  3.49 |
| Pen          |  1.99 |
+--------------+-------+

Pagination Example

Imagine a web page that shows 2 products per page. Here is how to query each page:

-- Page 1: rows 1–2
SELECT product_name FROM products
ORDER BY product_id
LIMIT 2 OFFSET 0;

-- Page 2: rows 3–4
SELECT product_name FROM products
ORDER BY product_id
LIMIT 2 OFFSET 2;

-- Page 3: rows 5–6
SELECT product_name FROM products
ORDER BY product_id
LIMIT 2 OFFSET 4;

The formula for offset is: offset = (page_number - 1) × rows_per_page

LIMIT with WHERE

LIMIT can be combined with WHERE to narrow results and then cap the number returned:

SELECT product_name, price
FROM products
WHERE price < 15
ORDER BY price ASC
LIMIT 3;

Key Points

• LIMIT n returns only the first n rows from the result set.
• Always combine LIMIT with ORDER BY to get meaningful results.
• OFFSET skips a set number of rows before returning results — used for pagination.
• The shorthand LIMIT offset, count is equivalent to LIMIT count OFFSET offset.
• Using LIMIT improves performance by reducing the amount of data retrieved from large tables.