Part 24: Restricting Row Counts: LIMIT & OFFSET

LIMIT and OFFSET restrict the number of rows returned, forming the basis of simple pagination.

1. Syntax (PostgreSQL, MySQL, SQLite)

-- Get top 5 highest paid employees:
SELECT first_name, salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 5;

-- Page 2 (Skip first 10 rows, take next 10):
SELECT product_id, product_name 
FROM products 
ORDER BY product_id 
LIMIT 10 OFFSET 10;

Key Takeaways

• Always pair LIMIT and OFFSET with an explicit ORDER BY to ensure deterministic results.

In Part 25, we will compare TOP in SQL Server vs FETCH FIRST in ANSI SQL!