Pagination — LIMIT vs TOP vs FETCH FIRST

basic

Your e-commerce app needs to paginate product listings (20 per page). The dev team uses PostgreSQL locally but production will run on SQL Server at a client site. Write portable pagination.

-- ✅ ANSI SQL:2008 — works on PostgreSQL 8.4+, SQL Server 2012+, Oracle 12c+

SELECT product_id, name, price

FROM products

ORDER BY name ASC

OFFSET 40 ROWS -- skip first 2 pages (page 3 start)

FETCH FIRST 20 ROWS ONLY;

-- ✅ PostgreSQL / MySQL — simple and readable

SELECT product_id, name, price

FROM products

ORDER BY name ASC

LIMIT 20 OFFSET 40;

-- ✅ SQL Server (pre-2012) — TOP with no offset support

SELECT TOP 20 product_id, name, price

FROM products

ORDER BY name ASC;

-- ⚠️ TOP has NO offset — requires subquery hack for pagination

-- ✅ SQL Server 2012+ — matches ANSI closely

SELECT product_id, name, price

FROM products

ORDER BY name ASC

OFFSET 40 ROWS

FETCH NEXT 20 ROWS ONLY; -- SQL Server uses NEXT, ANSI uses FIRST

-- ✅ Oracle pre-12c — the old ROWNUM nightmare

SELECT * FROM (

SELECT product_id, name, price,

ROWNUM AS rn

FROM (SELECT product_id, name, price FROM products ORDER BY name)

WHERE ROWNUM <= 60 -- page end

)

WHERE rn > 40; -- page start

-- ✅ Oracle 12c+ — same as ANSI FETCH FIRST

SELECT product_id, name, price

FROM products

ORDER BY name ASC

OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

Page 3 results (rows 41-60):

product_id | name | price