LIMIT restricts the number of rows returned. OFFSET skips rows. Essential for pagination.

Basic LIMIT

SELECT * FROM products LIMIT 10;

LIMIT with OFFSET

SELECT * FROM products LIMIT 10 OFFSET 20;

Pagination Pattern

-- Page 1 (items 1-10)

SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;

-- Page 2 (items 11-20)

SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;

-- Page 3 (items 21-30)

SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

Top N Results

SELECT * FROM products ORDER BY sales DESC LIMIT 5;

MySQL Shorthand

SELECT * FROM products LIMIT 20, 10; -- Same as LIMIT 10 OFFSET 20

SQL Server (TOP)

SELECT TOP 10 * FROM products;

Oracle (ROWNUM/FETCH)

SELECT * FROM products FETCH FIRST 10 ROWS ONLY;

Generate Paginated Queries

AI2sql handles pagination for any database.