MySQL LIMIT Clause

The LIMIT clause restricts how many rows a SELECT query returns. Even if a table holds thousands of records, LIMIT lets you fetch only the number you actually need. This is essential for performance, for previewing data, and for building pagination in web applications.

Syntax

-- Return n rows
SELECT columns FROM table_name LIMIT n;

-- Return n rows starting after skipping offset rows
SELECT columns FROM table_name LIMIT offset, n;

-- Same as above, clearer alternative syntax
SELECT columns FROM table_name LIMIT n OFFSET offset;

Basic LIMIT

Fetch only the first 3 rows from the students table:

SELECT name, city FROM students
LIMIT 3;

LIMIT with ORDER BY

The most common real-world use: get the top N records by a specific criterion.

-- Get the 3 oldest students
SELECT name, age FROM students
ORDER BY age DESC
LIMIT 3;

LIMIT with OFFSET — Pagination

The OFFSET tells MySQL how many rows to skip before it starts returning results. This is the foundation of pagination — showing data page by page.

Imagine you display 3 students per page:

-- Page 1: rows 1–3 (skip 0, take 3)
SELECT name, city FROM students
ORDER BY id ASC
LIMIT 3 OFFSET 0;

-- Page 2: rows 4–6 (skip 3, take 3)
SELECT name, city FROM students
ORDER BY id ASC
LIMIT 3 OFFSET 3;

-- Page 3: rows 7–9 (skip 6, take 3)
SELECT name, city FROM students
ORDER BY id ASC
LIMIT 3 OFFSET 6;

The formula for any page number is: OFFSET = (page_number - 1) × rows_per_page.

LIMIT with WHERE

LIMIT is applied after WHERE — it caps the result of the filtered set, not the whole table:

-- Get the 2 youngest students from Dhaka
SELECT name, age FROM students
WHERE city = 'Dhaka'
ORDER BY age ASC
LIMIT 2;

Key Points to Remember

• LIMIT n returns at most n rows from the result set.
• Always use ORDER BY with LIMIT when the order of results matters.
• LIMIT n OFFSET m skips the first m rows and returns the next n — the basis of pagination.
• LIMIT comes last in the clause order: SELECT → FROM → WHERE → ORDER BY → LIMIT.
• If fewer rows exist than the LIMIT value, MySQL simply returns however many rows are available — no error.