MySQL LIMIT and OFFSET

MySQL LIMIT restricts the number of rows returned by a query. To return the first 10 rows:

SELECT id, name, class, mark
FROM student
LIMIT 10;

To skip the first 20 rows and return the next 10:

SELECT id, name, class, mark
FROM student
LIMIT 20, 10;

The first value is the offset; the second is the number of rows to return. An offset of 20 skips rows 1–20, so the result starts with row 21.

MySQL LIMIT Syntax Top ↑

Return a fixed number of rows:

SELECT column_list
FROM table_name
LIMIT row_count;

Example:

SELECT id, name, class, mark
FROM student
LIMIT 8;

This returns at most eight rows.

LIMIT with OFFSET Top ↑

MySQL supports two common forms for skipping rows.

Comma syntax Top ↑

SELECT id, name, class, mark
FROM student
LIMIT 20, 10;

OFFSET syntax Top ↑

SELECT id, name, class, mark
FROM student
LIMIT 10 OFFSET 20;

Both queries skip 20 rows and return up to 10 rows.

LIMIT with ORDER BY Top ↑

Combine ORDER BY with LIMIT when you need a meaningful first or last set of rows.

SELECT id, name, class, mark
FROM student
ORDER BY class ASC, id ASC
LIMIT 10;

The query first defines the order, then returns the first ten rows from that ordered result.

LIMIT with WHERE Top ↑

The WHERE clause filters records before LIMIT is applied.

SELECT id, name, class, mark
FROM student
WHERE mark > 80
ORDER BY mark DESC, id ASC
LIMIT 10;

This returns at most ten students with marks above 80, highest mark first.

Get Top or Highest Records Top ↑

To return the highest mark:

SELECT id, name, mark
FROM student
ORDER BY mark DESC, id ASC
LIMIT 1;

To return the top three rows:

SELECT id, name, mark
FROM student
ORDER BY mark DESC, id ASC
LIMIT 3;

Second row is not always the second distinct highest value Top ↑

This query returns the second row after sorting:

SELECT id, name, mark
FROM student
ORDER BY mark DESC, id ASC
LIMIT 1 OFFSET 1;

If two students share the highest mark, the second row can have the same mark as the first. That is different from finding the second distinct highest mark. See second-highest record examples for that distinction.

Recently Added Records Top ↑

If an auto-increment ID increases with each insert, the latest five IDs can be returned with:

SELECT id, name, class, mark
FROM student
ORDER BY id DESC
LIMIT 5;

See MySQL AUTO_INCREMENT.

If the table has a date-time column such as dt, sort by that value and use a secondary key to make ties deterministic:

SELECT id, name, dt
FROM student
ORDER BY dt DESC, id DESC
LIMIT 5;

Use LIMIT for Pagination Top ↑

For page-based pagination, calculate the offset from the page number and number of rows per page.

offset = (page_number - 1) * rows_per_page

For 10 rows per page:

Page 3 can use:

SELECT id, name, class, mark
FROM student
ORDER BY id ASC
LIMIT 10 OFFSET 20;

LIMIT and OFFSET with PHP PDO Top ↑

Validate paging values as integers and bind them with PDO::PARAM_INT.

<?php
require 'config.php';

$page=filter_input(
    INPUT_GET,
    'page',
    FILTER_VALIDATE_INT,
    [
        'options' => [
            'default' => 1,
            'min_range' => 1
        ]
    ]
);

$per_page=10;
$offset=($page-1)*$per_page;

$stmt=$dbo->prepare(
    "SELECT id,name,class,mark
     FROM student
     ORDER BY id ASC
     LIMIT :limit OFFSET :offset"
);

$stmt->bindValue(
    ':limit',
    $per_page,
    PDO::PARAM_INT
);

$stmt->bindValue(
    ':offset',
    $offset,
    PDO::PARAM_INT
);

$stmt->execute();

while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    echo htmlspecialchars(
        (string)$row['name'],
        ENT_QUOTES,
        'Windows-1252'
    ).'<br>';
}

The page number is application input, so it is validated before the offset is calculated. Output values are escaped before being inserted into HTML.

Large OFFSET Performance Top ↑

OFFSET pagination is simple and works well for many applications, but large offsets can become expensive because the database may still need to locate and skip many earlier rows.

For example:

SELECT id, name
FROM student
ORDER BY id
LIMIT 20 OFFSET 100000;

For very large or continuously growing datasets, keyset pagination can be more efficient when the application can continue from the last seen ordered key:

SELECT id, name
FROM student
WHERE id > 5000
ORDER BY id ASC
LIMIT 20;

The best approach depends on the application, indexes, data size and navigation requirements.

LIMIT Is Different from BETWEEN Top ↑

LIMIT works with row positions in the ordered result. It does not filter by ID values.

This skips 20 result rows:

SELECT id, name
FROM student
ORDER BY id
LIMIT 10 OFFSET 20;

This filters by an actual column value:

SELECT id, name
FROM student
WHERE id BETWEEN 21 AND 30
ORDER BY id;

See SQL BETWEEN for value ranges.

LIMIT in Other Databases Top ↑

LIMIT is common in MySQL and several other database systems, but row-limiting syntax is not identical across all SQL databases. Other systems may use syntax such as FETCH FIRST, OFFSET ... FETCH or TOP.

When writing portable SQL, check the syntax supported by the target database rather than assuming the MySQL LIMIT form works everywhere.

Common LIMIT Mistakes Top ↑

Using LIMIT without ORDER BY for pagination Top ↑

Without a deterministic ORDER BY, rows can appear in an unpredictable order between requests.

Thinking OFFSET 20 means record ID 20 Top ↑

OFFSET counts result rows to skip. It has no direct relationship with an ID column.

Confusing the two LIMIT numbers Top ↑

In MySQL comma syntax, LIMIT 20,10 means offset 20 and row count 10.

Calling LIMIT 1 OFFSET 1 the second distinct highest value Top ↑

It returns the second sorted row. Tied values can mean the second row has the same value as the first.

Using huge OFFSET values without checking performance Top ↑

Large offsets can require substantial work. Consider indexes and alternative pagination designs when datasets become large.

Putting LIMIT before ORDER BY Top ↑

In a normal MySQL SELECT statement, ORDER BY appears before LIMIT.

Related SQL Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: