00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL LIMIT Clause

Last updated: 5 min read

The LIMIT clause restricts how many rows a query returns. Use it for "top 5" lists, for previews, and to split long results into pages. Combined with ORDER BY, it answers questions like who are the three highest scorers?

1. Syntax

syntax.sql 
SELECT ... FROM ... [WHERE ...] [ORDER BY ...]
LIMIT row_count;

LIMIT row_count OFFSET skip;     -- skip rows first
LIMIT skip, row_count;           -- same thing, MySQL short form
In the short form the order is LIMIT offset, count, the opposite of the long form. LIMIT 10, 5 skips 10 rows and returns 5.

2. Top N Rows

query.sql 
SELECT player, points FROM scores ORDER BY points DESC LIMIT 3;

Result: 3 rows

playerpoints
Bruno9650
Dev9650
Hiro9100

Bruno and Dev both have 9,650 points. When sort values tie, their order is not guaranteed, so add a tie-breaker column to make results repeatable:

query.sql 
SELECT player, points FROM scores ORDER BY points DESC, player ASC LIMIT 3;

Result: 3 rows

playerpoints
Bruno9650
Dev9650
Hiro9100

3. Why LIMIT Needs ORDER BY

Without ORDER BY, a table has no defined order: MySQL returns whichever rows are cheapest to read, which can change after updates, index changes or upgrades. LIMIT 3 alone means "any 3 rows", not "the first 3".

query.sql 
SELECT player FROM scores LIMIT 3;   -- some 3 rows: don't rely on which

Result: 3 rows

player
Aanya
Bruno
Chloe

4. OFFSET and Pagination

LIMIT and OFFSET split a sorted result into pagesTen sorted rows are split into pages of four. Page 1 is LIMIT 4 OFFSET 0, page 2 is LIMIT 4 OFFSET 4, page 3 is LIMIT 4 OFFSET 8 and contains the last two rows.row 1row 2row 3row 4row 5row 6row 7row 8row 9row 10page 1: LIMIT 4 OFFSET 0page 2: LIMIT 4 OFFSET 4page 3: LIMIT 4 OFFSET 8sorted result (ORDER BY …)OFFSET = (page − 1) × page size
Figure 1: pagination with LIMIT page_size OFFSET (page − 1) × page_size.
query.sql 
SELECT player, points FROM scores ORDER BY points DESC, player LIMIT 4 OFFSET 0;   -- page 1

Page 1

playerpoints
Bruno9650
Dev9650
Hiro9100
Felix8800
query.sql 
SELECT player, points FROM scores ORDER BY points DESC, player LIMIT 4 OFFSET 4;   -- page 2

Page 2

playerpoints
Aanya8120
Isla7750
Chloe7300
Emma6800
query.sql 
SELECT player, points FROM scores ORDER BY points DESC, player LIMIT 8, 4;        -- page 3, short form

Page 3

playerpoints
Jonas6400
Gita5900

Count the total to know how many pages there are:

query.sql 
SELECT COUNT(*) AS total_rows, CEIL(COUNT(*) / 4) AS pages FROM scores;

Result: 1 row

total_rowspages
103

5. LIMIT 1: the Single Best Row

query.sql 
SELECT player, played_on FROM scores ORDER BY played_on DESC, player_id DESC LIMIT 1;   -- most recent

Result: 1 row

playerplayed_on
Jonas2026-03-05
query.sql 
SELECT player, points FROM scores WHERE country = 'IN' ORDER BY points LIMIT 1;   -- lowest Indian score

Result: 1 row

playerpoints
Gita5900

6. Top N per Group

LIMIT applies to the whole result, not to each group. For "best player per country" use a window function and filter on its rank:

top-per-group.sql 
SELECT country, player, points
FROM (
  SELECT country, player, points,
         ROW_NUMBER() OVER (PARTITION BY country ORDER BY points DESC) AS rn
  FROM scores
) ranked
WHERE rn = 1
ORDER BY points DESC;

Result: 6 rows

countryplayerpoints
BRBruno9650
INDev9650
JPHiro9100
DEFelix8800
UKIsla7750
FRChloe7300

7. LIMIT in UPDATE and DELETE

A single number (no offset) is allowed in UPDATE and DELETE, usually with ORDER BY:

update-limit.sql 
UPDATE scores SET points = points + 500 ORDER BY points ASC LIMIT 2;   -- boost the two lowest
SELECT player, points FROM scores ORDER BY points LIMIT 3;

Result: 3 rows

playerpoints
Gita6400
Emma6800
Jonas6900
delete-limit.sql 
DELETE FROM scores ORDER BY played_on, player_id LIMIT 1;
SELECT COUNT(*) AS players_left FROM scores;

Result: 1 row

players_left
9

8. LIMIT in Sub-queries

MySQL doesn't allow LIMIT inside an IN (…) sub-query:

query.sql 
SELECT player FROM scores WHERE player_id IN (SELECT player_id FROM scores ORDER BY points DESC LIMIT 3);
Output 
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Wrap it in a derived table instead:

derived.sql 
SELECT s.player, s.points
FROM scores s
JOIN (SELECT player_id FROM scores ORDER BY points DESC LIMIT 3) top3 USING (player_id)
ORDER BY s.points DESC;

Result: 3 rows

playerpoints
Bruno9650
Dev9650
Hiro9100

9. Fast Pagination for Big Tables (Keyset)

OFFSET 100000 forces MySQL to read and throw away 100,000 rows, so deep pages get slower and slower. Keyset pagination remembers the last row shown and continues after it, using an index:

keyset-page1.sql 
-- page 1
SELECT player_id, player FROM scores ORDER BY player_id LIMIT 3;

Result: 3 rows

player_idplayer
2Bruno
3Chloe
4Dev
keyset-page2.sql 
-- next page: continue after the last player_id shown (4)
SELECT player_id, player FROM scores WHERE player_id > 4 ORDER BY player_id LIMIT 3;

Result: 3 rows

player_idplayer
5Emma
6Felix
7Gita
OFFSET paginationKeyset pagination
Jump to page 50YesNo, only next / previous
Speed on deep pagesGets slowerConstant
Stable when rows are insertedRows can shift or repeatStable

10. Pagination from Java

Pagination.java 
int pageSize = 4, page = 2;
String sql = "SELECT player, points FROM scores ORDER BY points DESC, player LIMIT ? OFFSET ?";
try (PreparedStatement ps = con.prepareStatement(sql)) {
    ps.setInt(1, pageSize);
    ps.setInt(2, (page - 1) * pageSize);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) System.out.println(rs.getString(1) + " " + rs.getInt(2));
    }
}
// Spring Data: repository.findAll(PageRequest.of(page - 1, pageSize, Sort.by("points").descending()))

11. Summary

  • LIMIT n returns at most n rows; LIMIT n OFFSET m (or LIMIT m, n) skips m first.
  • Always pair LIMIT with ORDER BY, plus a tie-breaker, for predictable results.
  • Page k of size n: LIMIT n OFFSET (k − 1) × n; use keyset pagination for large tables.
  • For top-N per group use ROW_NUMBER(); LIMIT isn't allowed in IN sub-queries.