LIMIT

Introduction

Real tables can hold millions of rows. Returning all of them at once is rarely necessary and can be slow — most applications only need a small "page" of results at a time, like the first 10 search results or the top 5 leaderboard entries.

The LIMIT clause restricts how many rows a query returns. Combined with an offset, it also enables pagination — showing different "pages" of the same result set.

Basic LIMIT Syntax

LIMIT goes at the very end of a query and takes a number: the maximum number of rows to return.

SELECT column1, column2
FROM table_name
LIMIT n;

We will use the same students table, now with a few more rows so pagination has something to demonstrate.

SELECT name, marks
FROM students
LIMIT 3;

+-------+-------+
| name  | marks |
+-------+-------+
| Alex  | 92    |
| Sam   | 74    |
| Priya | 88    |
+-------+-------+

Without an ORDER BY, the three rows returned are not guaranteed to be any particular three rows — which is why LIMIT is almost always paired with ORDER BY in real queries, as you will see shortly.

Pagination with OFFSET

To skip a number of rows before starting to return results, add an offset. MySQL supports two equivalent syntaxes.

-- Syntax 1: LIMIT offset, count
SELECT name, marks FROM students LIMIT 2, 3;

-- Syntax 2: LIMIT count OFFSET offset (equivalent)
SELECT name, marks FROM students LIMIT 3 OFFSET 2;

Both statements skip the first 2 rows and then return the next 3. Note that in the first syntax, the offset comes first; in the second syntax, the count comes first. Mixing them up is a common source of confusion.

Worked Example: Page 2

Imagine displaying students 3 per page, ordered by id. Page 1 shows the first 3 rows; page 2 should skip those 3 and show the next 3.

SELECT id, name
FROM students
ORDER BY id
LIMIT 3 OFFSET 0;

+----+-------+
| id | name  |
+----+-------+
| 1  | Alex  |
| 2  | Sam   |
| 3  | Priya |
+----+-------+

SELECT id, name
FROM students
ORDER BY id
LIMIT 3 OFFSET 3;

+----+--------+
| id | name   |
+----+--------+
| 4  | Jordan |
| 5  | Maya   |
| 6  | Ravi   |
+----+--------+

To reach page 2, the offset became 3 — the page size. In general, offset = pageSize * (pageNumber - 1). Note that ORDER BY id is what makes "page 2" mean the same thing every time the query runs.

Top N with ORDER BY + LIMIT

A very common pattern is finding the "top N" rows by some measure — the highest-scoring students, the most recent orders, and so on. This combines ORDER BY DESC with a small LIMIT.

SELECT name, marks
FROM students
ORDER BY marks DESC
LIMIT 3;

+-------+-------+
| name  | marks |
+-------+-------+
| Lena  | 95    |
| Alex  | 92    |
| Priya | 88    |
+-------+-------+

ORDER BY marks DESC puts the highest marks first, and LIMIT 3 keeps only the top three of those — giving exactly the three highest-scoring students in the whole table.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

LIMIT gives you precise control over how many rows a query returns, which is essential both for pagination and for "top N" style reporting. Always combine it with ORDER BY when the specific rows returned need to be predictable.

Next, you will learn how to remove duplicate rows from a result using DISTINCT.