SQL query to group consecutive records without destroying the chronological order

I have a table looking like this:

GR	I	VON	BIS
1	a	1	2
2	b	2	3
1	c	3	4
1	d	4	5
3	e	5	6

Column “GR” is the info I want to use for grouping.
Column “I” holds some none important info.
Column “VON” holds originally a date value – for readability I used a number here.
The same goes for column “BIS”.

So the records hold some information that is valid for the interval which is specified by “VON” and “BIS” and should be grouped by “GR”.

I’m trying to build a query to get the result like this:

GR	I	VON	BIS
1	a	1	2
2	b	2	3
1	c	3	5
3	e	5	6

That means, I want to merge rows with the same grouping information – in the example by merging records 3 and 4.

What I came up with was this query – but this only works for distinct groups:

select distinct  gr
      ,first_value(i) over(partition by gr order by von asc) i
      ,first_value(von) over(partition by gr order by von asc) von
      ,first_value(bis) over(partition by gr order by von desc) bis
  from table_test
;

With our test data it leads to this result, which is obviously wrong:

GR	I	VON	BIS
1	a	1	5
2	b	2	3
3	e	5	6

Any ideas how to solve this with a query? (I know I could do it with PL/SQL, but it needs to be pure SQL …)

Answer

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row processing:

SELECT *
FROM   table_test
MATCH_RECOGNIZE(
  ORDER BY von
  MEASURES
    FIRST(gr) AS gr,
    FIRST(i) AS i,
    FIRST(von) AS von,
    LAST(bis) AS bis
  PATTERN (same_gr+)
  DEFINE same_gr AS FIRST(gr) = gr
)

Which, for your sample data:

CREATE TABLE table_test (GR, I, VON, BIS) AS
SELECT 1, 'a', 1, 2 FROM DUAL UNION ALL
SELECT 2, 'b', 2, 3 FROM DUAL UNION ALL
SELECT 1, 'c', 3, 4 FROM DUAL UNION ALL
SELECT 1, 'd', 4, 5 FROM DUAL UNION ALL
SELECT 3, 'e', 5, 6 FROM DUAL;

Outputs:

GR	I	VON	BIS
1	a	1	2
2	b	2	3
1	c	3	5
3	e	5	6

db<>fiddle here