LIMIT and OFFSET

LIMIT and OFFSET

LIMIT specifies the maximum number of rows to return. OFFSET skips a number of rows before returning results.

💡 LIMIT and OFFSET are essential for pagination — showing results page by page.

Basic Syntax

-- Return only the first n rows
SELECT * FROM table LIMIT n;

-- Skip offset rows, then return limit rows
SELECT * FROM table LIMIT n OFFSET offset;

LIMIT Example

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

LIMIT with OFFSET

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

Pagination Pattern

-- Page 1
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;
-- Page 2
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;
-- Page 3
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

Database Differences

-- MySQL
SELECT * FROM products LIMIT 10 OFFSET 20;
SELECT * FROM products LIMIT 20, 10;  -- Alternative syntax

-- PostgreSQL
SELECT * FROM products LIMIT 10 OFFSET 20;

-- SQL Server
SELECT TOP 10 * FROM products;

-- Oracle
SELECT * FROM products WHERE ROWNUM <= 10;

⚠️ Always use ORDER BY with LIMIT. Without ordering, the rows LIMIT returns are unpredictable.

✏️ Exercise: Find the 5 most expensive products from a 'products' table

See Solution

SELECT * FROM products
ORDER BY price DESC
LIMIT 5;

✅ Key Takeaways

1. LIMIT restricts the number of rows returned
2. OFFSET skips rows before returning results
3. Essential for pagination in applications
4. Always use with ORDER BY for predictable results
5. Syntax varies between different database systems