SQL Pagination with LIMIT and OFFSET

Combine both to slice a sorted result into pages — exactly how most app UIs implement 'page 2, page 3...'

What & Why

Pagination combines LIMIT (page size) with OFFSET (which page). For a page size of 2: page 1 is OFFSET 0, page 2 is OFFSET 2, page 3 is OFFSET 4 — the offset is always (page_number - 1) * page_size.

See How It Works

BUSINESS QUESTION

Page 2 of a 2-per-page campaign listing, sorted by spend descending.

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

EXAMPLE QUERY

SELECT name, spend FROM marketing.campaigns
ORDER BY spend DESC
LIMIT 2 OFFSET 2;

RESULT — page 2

name	spend
Finance Retargeting	50000.00
Retention Webinar	45000.00

Page 1 (OFFSET 0) would have returned Enterprise Search and Spring Launch instead — the two highest-spend campaigns.

Now You Try

Practice this concept

Marketing wants page three of the lead directory with 25 leads per page, ordered newest first.

Available schema

marketing

Prefix tables with marketing.table_name.

idsourcecountrycreated_at

marketing.leads

Column	Type
id	integer
campaign_id	integer
email	text
created_at	timestamp with time zone
qualified_at	timestamp with time zone
converted_at	timestamp with time zone
lead_score	integer
source	text
country	text
archive_status	text

query.sql

Beginner business practice