SQL
Efficient Pagination with ROW_NUMBER for SQL
Learn to paginate query results efficiently or retrieve the top N records per group using SQL window functions like ROW_NUMBER, a powerful and flexible technique.
SELECT * FROM (
SELECT
column1, column2,
ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY order_column DESC) as rn
FROM
your_table
) AS subquery
WHERE
rn BETWEEN 11 AND 20; -- For page 2, assuming 10 items per page
-- To get top N per group (e.g., top 3 products per category):
-- SELECT * FROM (
-- SELECT
-- product_id, product_name, category_id,
-- ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) as rn
-- FROM
-- products
-- ) AS subquery
-- WHERE
-- rn <= 3;
How it works: The ROW_NUMBER() window function assigns a unique sequential integer to rows within a specified partition (group) based on an ordering. The PARTITION BY clause divides the rows into groups, and ORDER BY defines the sequence within each group. By wrapping this in a subquery, you can then filter on the generated row number (rn) to implement pagination (e.g., 'WHERE rn BETWEEN X AND Y') or retrieve the top N items per group (e.g., 'WHERE rn <= N').