Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE reprex_segment_data
  (original_order, desired_order, segment_id, record_time) AS
SELECT 1, 1, '25-52-21', TO_DATE('7:57:18', 'HH24:MI:SS') FROM DUAL UNION ALL
SELECT 2, 2, '45-85-44', TO_DATE('7:57:26', 'HH24:MI:SS') FROM DUAL UNION ALL
SELECT 5, 3, '51-1-97',  TO_DATE('8:04:12', 'HH24:MI:SS') FROM DUAL UNION ALL
SELECT 4, 4, '81-24-87', TO_DATE('8:04:12', 'HH24:MI:SS') FROM DUAL UNION ALL
SELECT 3, 5, '18-11-88', TO_DATE('8:04:12', 'HH24:MI:SS') FROM DUAL UNION ALL
SELECT 7, 6, '79-60-16', TO_DATE('8:04:54', 'HH24:MI:SS') FROM DUAL UNION ALL
SELECT 6, 7, '12-80-48', TO_DATE('8:04:54', 'HH24:MI:SS') FROM DUAL UNION ALL
SELECT 8, 8, '11-58-36', TO_DATE('8:05:12', 'HH24:MI:SS') FROM DUAL UNION ALL
SELECT 9, 9, '90-49-72', TO_DATE('8:05:36', 'HH24:MI:SS') FROM DUAL;
9 rows affected

CREATE TABLE reprex_neighbors_table (segment_id, next_segment_id) AS
  SELECT '25-52-21', '45-85-44' FROM DUAL UNION ALL
  SELECT '45-85-44', '51-1-97'  FROM DUAL UNION ALL
  SELECT '51-1-97',  '81-24-87' FROM DUAL UNION ALL
  SELECT '81-24-87', '18-11-88' FROM DUAL UNION ALL
  SELECT '18-11-88', '79-60-16' FROM DUAL UNION ALL
  SELECT '79-60-16', '12-80-48' FROM DUAL UNION ALL
  SELECT '12-80-48', '11-58-36' FROM DUAL UNION ALL
  SELECT '11-58-36', '90-49-72' FROM DUAL UNION ALL
  SELECT '81-24-87', '69-67-78' FROM DUAL;
9 rows affected

WITH order_ids (segment_id, order_id) AS (
  SELECT segment_id,
         MIN(depth + type)
  FROM   (
    SELECT segment_id,
           next_segment_id,
           LEVEL AS depth
    FROM   reprex_neighbors_table
    START WITH segment_id NOT IN (SELECT next_segment_id FROM reprex_neighbors_table)
    CONNECT BY PRIOR next_segment_id = segment_id
    ORDER SIBLINGS BY segment_id
  )
  UNPIVOT (segment_id FOR type IN (segment_id AS 0, next_segment_id AS 1))
  WHERE  type = 1
  OR     depth = 1
  GROUP BY segment_id
)
SELECT d.*
FROM   reprex_segment_data d
       INNER JOIN order_ids o
       ON d.segment_id = o.segment_id
ORDER BY
       d.record_time,
       o.order_id


ORIGINAL_ORDER
DESIRED_ORDER
SEGMENT_ID
RECORD_TIME


1
1
25-52-21
2024-04-01 07:57:18

2
2
45-85-44
2024-04-01 07:57:26

5
3
51-1-97
2024-04-01 08:04:12

4
4
81-24-87
2024-04-01 08:04:12

3
5
18-11-88
2024-04-01 08:04:12

7
6
79-60-16
2024-04-01 08:04:54

6
7
12-80-48
2024-04-01 08:04:54

8
8
11-58-36
2024-04-01 08:05:12

9
9
90-49-72
2024-04-01 08:05:36

ORIGINAL_ORDER	DESIRED_ORDER	SEGMENT_ID	RECORD_TIME
1	1	25-52-21	2024-04-01 07:57:18
2	2	45-85-44	2024-04-01 07:57:26
5	3	51-1-97	2024-04-01 08:04:12
4	4	81-24-87	2024-04-01 08:04:12
3	5	18-11-88	2024-04-01 08:04:12
7	6	79-60-16	2024-04-01 08:04:54
6	7	12-80-48	2024-04-01 08:04:54
8	8	11-58-36	2024-04-01 08:05:12
9	9	90-49-72	2024-04-01 08:05:36