Pagination with analytics

U

Usage of analytic function for splitting rows to pages.

WITH p AS (
    SELECT
        2 AS page_id,               -- setup for pagination
        5 AS page_size
    FROM DUAL
),
d AS (
    SELECT 'A' AS group_name, 11 AS id FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 12 AS id FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 13 AS id FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 21 AS id FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 22 AS id FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 23 AS id FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 24 AS id FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 31 AS id FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 32 AS id FROM DUAL UNION ALL
    SELECT 'D' AS group_name, 41 AS id FROM DUAL UNION ALL
    SELECT 'D' AS group_name, 42 AS id FROM DUAL UNION ALL
    SELECT 'D' AS group_name, 43 AS id FROM DUAL
)
SELECT d.*
FROM (
    SELECT
        d.*,
        ROW_NUMBER() OVER (ORDER BY d.group_name, d.id) AS row#
    FROM d
) d
JOIN p ON 1 = 1
WHERE d.row# BETWEEN (p.page_id - 1) * p.page_size + 1 AND p.page_id * p.page_size
ORDER BY 1, 2;