Select previous/next rows

A

Analytic functions for selecting previous or next rows.

WITH 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 '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 'C' AS group_name, 31 AS id FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 32 AS id FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 33 AS id FROM DUAL UNION ALL
    SELECT 'D' AS group_name, 41 AS id FROM DUAL
)
SELECT
    d.*,
    LAG(d.id,  1, NULL) OVER (      -- shift by 1, NULL on not found
        PARTITION BY d.group_name
        ORDER BY d.id
    ) AS group_prev,
    --
    LEAD(d.id, 1, NULL) OVER (
        PARTITION BY d.group_name
        ORDER BY d.id
    ) AS group_next,
    --
    MIN(d.id) OVER (PARTITION BY d.group_name) AS group_min,
    MAX(d.id) OVER (PARTITION BY d.group_name) AS group_max,
    --
    FIRST_VALUE(d.id IGNORE NULLS) OVER (
        PARTITION BY d.group_name
        ORDER BY d.id
    ) AS group_first,
    --
    LAST_VALUE(d.id IGNORE NULLS) OVER (
        PARTITION BY d.group_name
        ORDER BY d.id
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS group_last,
    --
    NTH_VALUE(d.id, 1) OVER (
        PARTITION BY d.group_name
        ORDER BY d.id
    ) AS nth_1st,
    --
    NTH_VALUE(d.id, 2) OVER (
        PARTITION BY d.group_name
        ORDER BY d.id
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS nth_2nd
FROM d
ORDER BY 1, 2;