Portable Pagination - LIMIT vs TOP vs OFFSET/FETCH

intermediate

Pagination is one of the most common compatibility issues. MySQL/PostgreSQL use LIMIT, SQL Server uses TOP (old) or OFFSET/FETCH (new), Oracle uses ROWNUM (old) or OFFSET/FETCH (new). Writing portable pagination requires either using OFFSET/FETCH (SQL:2008 standard) or conditional SQL based on database type. This is critical for SaaS products supporting multiple databases.

-- Problem: Different pagination syntax across databases

-- MySQL / PostgreSQL / SQLite: LIMIT

SELECT user_id, username, email

FROM users

ORDER BY user_id

LIMIT 10 OFFSET 20;

-- Get rows 21-30 (skip 20, take 10)

-- SQL Server (old): TOP (no offset support)

SELECT TOP 10 user_id, username, email

FROM users

WHERE user_id > 20 -- Manual offset (requires unique key)

ORDER BY user_id;

-- Workaround: Use WHERE to skip rows

-- SQL Server (2012+): OFFSET/FETCH

SELECT user_id, username, email

FROM users

ORDER BY user_id

OFFSET 20 ROWS

FETCH NEXT 10 ROWS ONLY;

-- SQL:2008 standard syntax

-- Oracle (old): ROWNUM

SELECT user_id, username, email

FROM (

SELECT user_id, username, email, ROWNUM as rn

FROM (

SELECT user_id, username, email

FROM users

ORDER BY user_id

)

WHERE ROWNUM <= 30

)

WHERE rn > 20;

-- Complex: Nested subqueries

-- Oracle (12c+): OFFSET/FETCH

SELECT user_id, username, email

FROM users

ORDER BY user_id