By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (id_1, id_2, num, id_3) AS
SELECT 'A1' ID_1, 'B1' ID_2, 10 NUM, 'X' ID_3 FROM DUAL UNION ALL
SELECT 'A1' ID_1, 'B2' ID_2, 20 NUM, 'Y' ID_3 FROM DUAL UNION ALL
SELECT 'A1' ID_1, 'B3' ID_2, 30 NUM, 'Z' ID_3 FROM DUAL UNION ALL
SELECT 'C1' ID_1, 'B1' ID_2, 1 NUM, 'Q' ID_3 FROM DUAL UNION ALL
SELECT 'C1' ID_1, 'B2' ID_2, 2 NUM, 'W' ID_3 FROM DUAL UNION ALL
SELECT 'C1' ID_1, 'B3' ID_2, 3 NUM, 'E' ID_3 FROM DUAL UNION ALL
SELECT 'C1' ID_1, 'B4' ID_2, 4 NUM, 'R' ID_3 FROM DUAL;
7 rows affected
SELECT id_1,
MIN(id_2) KEEP (DENSE_RANK FIRST ORDER BY num) AS min_id_2,
MIN(num) AS min_num,
MIN(id_3) KEEP (DENSE_RANK FIRST ORDER BY num, id_2) AS min_id_3,
MAX(id_2) KEEP (DENSE_RANK LAST ORDER BY num) AS max_id_2,
MAX(num) AS max_num,
MAX(id_3) KEEP (DENSE_RANK LAST ORDER BY num, id_2) AS max_id_3
FROM table_name
GROUP BY id_1;
| ID_1 | MIN_ID_2 | MIN_NUM | MIN_ID_3 | MAX_ID_2 | MAX_NUM | MAX_ID_3 |
|---|---|---|---|---|---|---|
| A1 | B1 | 10 | X | B3 | 30 | Z |
| C1 | B1 | 1 | Q | B4 | 4 | R |