SQL Server 2019

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

select *
into t
from (values (1 , 'Red'),
             (2 , 'Red'),
             (3 , 'Red'),
             (4 , 'Red'),
             (5 , 'Red'),
             (6 , 'Green'),
             (7 , 'Green'),
             (8 , 'Green'),
             (9 , 'Green'),
             (10, 'Red'),
             (11, 'Red'),
             (12, 'Red'),
             (13, 'Red'),
             (14, 'Green'),
             (15, 'Green'),
             (16, 'Green'),
             (17, 'Blue'),
             (18, 'Blue'),
             (19, 'Red'),
             (20, 'Blue')
    ) v(id, colour)
20 rows affected

select colour, min(id), max(id)
from (select t.*,
             row_number() over (order by id) as seqnum,
             row_number() over (partition by colour order by id) as seqnum_c
      from t
     ) t
group by colour, (seqnum - seqnum_c)


colour
(No column name)
(No column name)


Red
1
5

Red
10
13

Green
6
9

Green
14
16

Red
19
19

Blue
17
18

Blue
20
20


-- to explain how it works

select *
from (select t.*,
             row_number() over (order by id) as seqnum,
             row_number() over (partition by colour order by id) as seqnum_c
      from t
     ) t
order by id


id
colour
seqnum
seqnum_c


1
Red
1
1

2
Red
2
2

3
Red
3
3

4
Red
4
4

5
Red
5
5

6
Green
6
1

7
Green
7
2

8
Green
8
3

9
Green
9
4

10
Red
10
6

11
Red
11
7

12
Red
12
8

13
Red
13
9

14
Green
14
5

15
Green
15
6

16
Green
16
7

17
Blue
17
1

18
Blue
18
2

19
Red
19
10

20
Blue
20
3

id	colour	seqnum	seqnum_c
1	Red	1	1
2	Red	2	2
3	Red	3	3
4	Red	4	4
5	Red	5	5
6	Green	6	1
7	Green	7	2
8	Green	8	3
9	Green	9	4
10	Red	10	6
11	Red	11	7
12	Red	12	8
13	Red	13	9
14	Green	14	5
15	Green	15	6
16	Green	16	7
17	Blue	17	1
18	Blue	18	2
19	Red	19	10
20	Blue	20	3