MySQL LIMIT and OFFSET: Return Only the First N Rows

LIMIT restricts how many rows a query returns.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

query.sql

SQL

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

Output

OUTPUT

+---------------+--------+
| name          | price  |
+---------------+--------+
| Backpack      | 999.00 |
| Desk Lamp     | 650.00 |
| Laptop Sleeve | 450.00 |
+---------------+--------+
3 rows in set

The three most expensive products. LIMIT should almost always be used together with ORDER BY. Without it, you get an arbitrary set of rows.

OFFSET: skip rows

query.sql

SQL

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

Output

OUTPUT

+--------------+--------+
| name         | price  |
+--------------+--------+
| Water Bottle | 220.00 |
| Marker Set   | 145.00 |
| Notebook     |  80.00 |
+--------------+--------+
3 rows in set

LIMIT 3 OFFSET 3 means “skip the first 3 rows, then return the next 3”. MySQL also accepts a shorter form that puts the offset first:

query.sql

SQL

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

Output

OUTPUT

+--------------+--------+
| name         | price  |
+--------------+--------+
| Water Bottle | 220.00 |
| Marker Set   | 145.00 |
| Notebook     |  80.00 |
+--------------+--------+
3 rows in set

LIMIT 3, 3 is LIMIT offset, count, which is the opposite order of the English reading, and a classic source of mistakes. Prefer LIMIT count OFFSET offset, as it reads clearly. It is also the form PostgreSQL uses.

The cheapest and the newest

query.sql

SQL

1
2

SELECT name, price FROM products ORDER BY price LIMIT 1;
SELECT name, added_on FROM products ORDER BY added_on DESC, id DESC LIMIT 1;

Output

OUTPUT

+---------+-------+
| name    | price |
+---------+-------+
| Gel Pen | 15.50 |
+---------+-------+
1 row in set

+--------------+------------+
| name         | added_on   |
+--------------+------------+
| Sticky Notes | 2026-04-18 |
+--------------+------------+
1 row in set

LIMIT in UPDATE and DELETE

MySQL allows LIMIT (and ORDER BY) in single-table UPDATE and DELETE. It is useful for working through a large table in batches:

query.sql

SQL

1
2

UPDATE products SET stock = stock + 10 ORDER BY price LIMIT 2;
SELECT name, price, stock FROM products ORDER BY price LIMIT 3;

Output

OUTPUT

+--------------+-------+-------+
| name         | price | stock |
+--------------+-------+-------+
| Gel Pen      | 15.50 |   310 |
| Sticky Notes | 35.00 |   220 |
| Notebook     | 80.00 |   120 |
+--------------+-------+-------+
3 rows in set

The two cheapest products received more stock. This is MySQL-specific and not allowed in multi-table updates.

Parameters

In a prepared statement, the values after LIMIT and OFFSET can be placeholders in MySQL (server-side prepared statements), but some drivers need them to be integers, not strings. In PHP PDO with emulated prepares, bind them as PDO::PARAM_INT, otherwise you get a syntax error because the value is quoted as '10' (PDO Prepared Statements).

The standard form

SQL standard syntax is FETCH FIRST n ROWS ONLY. MySQL does not support it. Other databases do (PostgreSQL, Oracle, SQL Server). MySQL has only LIMIT.

Large numbers

LIMIT on a big sorted query still has to find the sorted top rows. With an index on the sort column, MySQL reads only a few rows. Without it, MySQL sorts the whole result and throws most of it away. See Sorting and Indexes.

Common mistakes

LIMIT without ORDER BY: the “first” rows are not defined.
LIMIT 10, 5 read as “10 rows from row 5”. It is 5 rows after skipping 10.
A huge OFFSET (for example 1,000,000): MySQL reads and discards a million rows. Use keyset pagination (Pagination).
LIMIT with a value taken straight from user input without validation: cast it to an integer and set a maximum.

lightbulb

Cap result sizes

Set a maximum page size in your application (for example 100). One request for LIMIT 1000000 can hurt everyone else on the server.