LIMIT / OFFSET

-- Limits the number of rows returned (MySQL, 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;

-- SQL Server uses TOP instead.
SELECT TOP count column FROM table_name;

-- Retrieves the top 3 employees by salary (highest first).
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;

-- Pagination example: 10 rows per page, retrieves page 2.
SELECT name, hire_date FROM employees ORDER BY hire_date DESC LIMIT 10 OFFSET 10;

-- Same result using MySQL shorthand syntax.
SELECT name, hire_date FROM employees ORDER BY hire_date DESC LIMIT 10, 10;

-- Retrieves page 3 (rows 21 through 30).
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

-- Example result of: SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;
-- +----------+--------+
-- | name     | salary |
-- +----------+--------+
-- | Hanako   | 380000 |
-- | Ichiro   | 350000 |
-- | Jiro     | 320000 |
-- +----------+--------+

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

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

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

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

SQL Server uses OFFSET / FETCH (version 2012 and later). ORDER BY is required.

SELECT name, salary FROM employees ORDER BY salary DESC
OFFSET 20 ROWS FETCH NEXT 10 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.