LIMIT / OFFSET

Limits the number of rows returned (MySQL, PostgreSQL, SQLite, etc.).

SELECT column FROM table_name LIMIT count;

Specifies a starting position and row count (OFFSET is zero-based).

SELECT column FROM table_name LIMIT count OFFSET start;

MySQL shorthand syntax (LIMIT start, count).

SELECT column FROM table_name LIMIT start, count;

The following 'employees' table is used in the examples below.

Retrieves the top 3 employees by salary (highest first).

sample_limit_offset.sql

SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;

+--------+--------+
| name   | salary |
+--------+--------+
| user_a | 380000 |
| user_c | 350000 |
| user_e | 320000 |
+--------+--------+
3 rows in set

Pagination example (3 rows per page, retrieves page 2).

sample_limit_offset.sql

SELECT name, hire_date FROM employees ORDER BY hire_date DESC LIMIT 3 OFFSET 3;

+--------+------------+
| name   | hire_date  |
+--------+------------+
| user_c | 2020-06-01 |
| user_a | 2019-09-15 |
+--------+------------+
2 rows in set

Same result using MySQL shorthand syntax.

sample_limit_offset.sql

SELECT name, hire_date FROM employees ORDER BY hire_date DESC LIMIT 3, 3;

+--------+------------+
| name   | hire_date  |
+--------+------------+
| user_c | 2020-06-01 |
| user_a | 2019-09-15 |
+--------+------------+
2 rows in set

Retrieves the earliest hired employee.

sample_limit_offset.sql

SELECT name, hire_date FROM employees ORDER BY hire_date ASC LIMIT 1;

+--------+------------+
| name   | hire_date  |
+--------+------------+
| user_a | 2019-09-15 |
+--------+------------+
1 row in set

PostgreSQL and SQLite support the same LIMIT / OFFSET syntax as MySQL.

SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3 OFFSET 3;

Oracle uses FETCH FIRST (version 12c and later).

SELECT name, salary FROM employees ORDER BY salary DESC
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

LIMIT and OFFSET are essential for implementing pagination. Given a page number ($page) and a per-page count ($per_page), the offset can be calculated as ($page - 1) * $per_page.

Using LIMIT / OFFSET without ORDER BY does not guarantee the order of results. Always pair them with ORDER BY to ensure consistent ordering when paginating.

With large datasets, performance degrades as the OFFSET value increases. For high-volume data, consider cursor-based pagination (filtering by the last seen ID) as an alternative.