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,
--
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;
```