Pagination Queries

Code

-- OFFSET pagination (simple but inefficient)
SELECT id, name, email
FROM users
ORDER BY id
LIMIT 20 OFFSET 40; -- Page 3, 20 items per page

-- Cursor pagination (efficient)
SELECT id, name, email
FROM users
WHERE id > 100  -- id of the last record on the previous page
ORDER BY id
LIMIT 20;

-- Calculate total pages
SELECT CEIL(COUNT(*) / 20.0) AS total_pages
FROM users
WHERE active = true;

-- Return data and total count
SELECT id, name, email FROM users ORDER BY id LIMIT 20 OFFSET 0;
SELECT COUNT(*) AS total FROM users;

-- Window function pagination
WITH paginated AS (
    SELECT *, COUNT(*) OVER () AS total_count,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM users
)
SELECT * FROM paginated
WHERE rn BETWEEN 1 AND 20;

Explanation

OFFSET pagination is simple but slow for large data; cursor pagination uses WHERE conditions for efficient paging.

More SQL Snippets