Paginate Query Results with SQL LIMIT and OFFSET Clauses

-- Retrieve the first 10 records (page 1)
SELECT
    id,
    name,
    created_at
FROM
    products
ORDER BY
    id
LIMIT 10 OFFSET 0;

-- Retrieve the next 10 records (page 2, skip first 10)
SELECT
    id,
    name,
    created_at
FROM
    products
ORDER BY
    id
LIMIT 10 OFFSET 10;

-- Retrieve records for page N (e.g., page 5, 10 items per page)
-- LIMIT [items_per_page] OFFSET ([page_number] - 1) * [items_per_page];
SELECT
    id,
    name,
    created_at
FROM
    products
ORDER BY
    id
LIMIT 10 OFFSET (5 - 1) * 10;

How it works: This snippet demonstrates how to implement pagination using SQL's `LIMIT` and `OFFSET` clauses. `LIMIT` specifies the maximum number of rows to return, while `OFFSET` specifies how many rows to skip from the beginning of the result set. It's crucial to use an `ORDER BY` clause with pagination to ensure a consistent and predictable order of results across different pages. Examples show fetching the first page, subsequent pages, and a generalized formula for any given page number and items per page.