How to use LIMIT and OFFSET

Short answer

LIMIT restricts the number of rows returned. OFFSET skips a specified number of rows before returning results, enabling pagination.

Steps

1. Top N rows: SELECT * FROM products ORDER BY price DESC LIMIT 10;
2. Skip and take: SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 20;
3. Page 3 with page size 10: LIMIT 10 OFFSET 20
4. Alternative syntax in SQL Server uses OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY.
5. Combine with ORDER BY to ensure deterministic pagination.

Tips

• Always use ORDER BY with LIMIT and OFFSET; without it, row order is undefined.
• Large OFFSET values degrade performance because the database still scans and discards rows.

Common issues

• Offset-based pagination drifts if new rows are inserted between page requests.
• Keyset pagination using WHERE id > last_seen_id is more efficient for large datasets.r