Oracle SQL - Find origin ID of autoincrement column

Question

There's a table on my ERP database that has data about certain events. It has the start date, end date and a column shows if the event is a continuation of a previous one (sequential_id references unique_id). Here's an example:

unique_id	start_date	end_date	sequential_id
001	2021-01-01	2021-01-15
002	2021-02-01	2021-02-16	001
003	2021-03-01	2021-03-17	002
004	2021-03-10	2021-03-11
005	2021-03-19

In the example above, rows 001, 002 and 003 are all part of the same event, and 004/005 are unique events, with no sequences. How can I group the data in a way that the output is like this:

origin_id	start_date	end_date
001	2021-01-01	2021-03-17
004	2021-03-10	2021-03-11
005	2021-03-19

I've tried using group by, but due to sequential_id being auto incremental, it didn't work.

Thanks in advance.

Answer 1

You can use modern match_recognize which is an optimal solution for such tasks:

Pattern Recognition With MATCH_RECOGNIZE

DBFiddle

select *
from t
match_recognize(
  measures 
     first(unique_id) start_unique_id,
     first(start_date) start_date,
     last(end_date) end_date
  pattern (strt nxt*)
  define nxt as sequential_id=prev(unique_id)
);