SQL - LIMIT/TOP/OFFSET

Overview

Different engines paginate differently. Use LIMIT for MySQL/SQLite and TOP or OFFSET/FETCH for SQL Server.

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

-- SQL Server (2012+)
SELECT * FROM employees ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
-- or older style
SELECT TOP (10) * FROM employees ORDER BY id;

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

Prerequisite

Load the Standard Test Data.

Expected Output

With ORDER BY id, LIMIT 10 OFFSET 20 on the small seed dataset will typically return no rows (fewer than 21 rows). For OFFSET 0 FETCH 10 (SQL Server) or LIMIT 10 (others), expect the first up to 10 rows.