SQL

Efficient Pagination with ROW_NUMBER() in SQL

Learn to implement robust and efficient pagination in SQL using window functions like ROW_NUMBER(). This method ensures consistent ordering and avoids OFFSET/LIMIT pitfalls.

WITH OrderedItems AS (
    SELECT
        id,
        name,
        price,
        ROW_NUMBER() OVER (ORDER BY created_at DESC, id ASC) AS rn
    FROM
        products
    WHERE
        category = 'electronics'
)
SELECT
    id,
    name,
    price
FROM
    OrderedItems
WHERE
    rn BETWEEN 11 AND 20; -- For page 2, assuming 10 items per page
How it works: This snippet demonstrates advanced pagination using a Common Table Expression (CTE) and the `ROW_NUMBER()` window function. It assigns a sequential row number to each product based on `created_at` and `id` for stable sorting. The outer query then filters these numbered rows to retrieve a specific page, providing more reliable pagination than simple `OFFSET` and `LIMIT`, especially with large datasets or non-unique sort keys.

Related SQL Snippets

Need help integrating this into your project?

Our team of expert developers can help you build your custom application from scratch.

Hire DigitalCodeLabs