MySQL 8.0

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

select version();


version()


8.0.19


create table mytable(id int, low int, high int);
insert into mytable (id, low, high)
values
(1,2,7), (1,3,4), (1,7,8), (1,7,10), (1,12,13), (1,13,20), (1,20,24),
         (2,3,4), (2,7,8), (2,7,10), (2,12,13), (2,13,20), (2,20,24);
with recursive numbered as
(
  select t.*, row_number() over (partition by id order by low) as rn
  from mytable t
)
, cte (id, rn, low, high, highest, tag) as
(
  select id, rn, low, high, high, 1 from numbered where rn = 1
  union all
  select 
    n.id, n.rn, n.low, n.high, 
    case when n.low <= c.highest then greatest(n.high, c.highest) else n.high end,
    case when n.low <= c.highest then c.tag else c.tag + 1 end
  from cte c
  join numbered n on n.id = c.id and n.rn = c.rn + 1
)
select id, low, high, tag
from cte
order by id, low, high;


id
low
high
tag


1
2
7
1

1
3
4
1

1
7
8
1

1
7
10
1

1
12
13
2

1
13
20
2

1
20
24
2

2
3
4
1

2
7
8
2

2
7
10
2

2
12
13
3

2
13
20
3

2
20
24
3

id	low	high	tag
1	2	7	1
1	3	4	1
1	7	8	1
1	7	10	1
1	12	13	2
1	13	20	2
1	20	24	2
2	3	4	1
2	7	8	2
2	7	10	2
2	12	13	3
2	13	20	3
2	20	24	3

id	low	high	tag
1	2	7	1
1	3	4	1
1	7	8	1
1	7	10	1
1	12	13	2
1	13	20	2
1	20	24	2
2	3	4	1
2	7	8	2
2	7	10	2
2	12	13	3
2	13	20	3
2	20	24	3

id	low	high	tag
1	2	7	1
1	3	4	1
1	7	8	1
1	7	10	1
1	12	13	2
1	13	20	2
1	20	24	2
2	3	4	1
2	7	8	2
2	7	10	2
2	12	13	3
2	13	20	3
2	20	24	3