Oracle 11g Release 2

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 mytable (
  ID   int,
  TIME   timestamp,              
  SPEED int  
);
insert into mytable
select 1 as ID,  TIMESTAMP '2024-01-01 09:00:02' as TIME, 0 as SPEED from dual union all
select  3,   TIMESTAMP '2024-01-01 09:00:03',     0 from dual union all
select  6,   TIMESTAMP '2024-01-01 09:00:04',     2 from dual union all
select  8,  TIMESTAMP  '2024-01-01 09:00:09',    11 from dual union all
select 14,  TIMESTAMP  '2024-01-01 09:00:10',    15 from dual union all
select 11,   TIMESTAMP '2024-01-01 09:00:22',    22 from dual union all
select 12,   TIMESTAMP '2024-01-01 09:00:28',     4 from dual union all
select 10,   TIMESTAMP '2024-01-01 09:00:32',     0 from dual union all
select 15,  TIMESTAMP  '2024-01-01 09:00:33',     2 from dual union all
select 16,   TIMESTAMP '2024-01-01 09:00:34',     8 from dual union all
select 17,  TIMESTAMP '2024-01-01 09:00:35',    12 from dual union all
select 18,   TIMESTAMP '2024-01-01 09:00:38',    3 from dual
12 rows affected

SELECT 	ID, TIME, SPEED,
   1+ SUM(CASE WHEN minSpeed = prevSpeed THEN 0 ELSE 1 END)
   OVER (ORDER BY TIME) ALT_RANGE_NO

FROM (
  SELECT ID, TIME, SPEED, minSpeed, Lag(minSpeed, 1, minSpeed) over(ORDER BY TIME)  prevSpeed
  FROM (
    SELECT ID, TIME, SPEED, CASE WHEN SPEED < 5 THEN 1 ELSE 0 END as minSpeed
    FROM mytable
  )
  ORDER BY TIME
)


ID
TIME
SPEED
ALT_RANGE_NO


1
01-JAN-24 09.00.02.000000 AM
0
1

3
01-JAN-24 09.00.03.000000 AM
0
1

6
01-JAN-24 09.00.04.000000 AM
2
1

8
01-JAN-24 09.00.09.000000 AM
11
2

14
01-JAN-24 09.00.10.000000 AM
15
2

11
01-JAN-24 09.00.22.000000 AM
22
2

12
01-JAN-24 09.00.28.000000 AM
4
3

10
01-JAN-24 09.00.32.000000 AM
0
3

15
01-JAN-24 09.00.33.000000 AM
2
3

16
01-JAN-24 09.00.34.000000 AM
8
4

17
01-JAN-24 09.00.35.000000 AM
12
4

18
01-JAN-24 09.00.38.000000 AM
3
5

ID	TIME	SPEED	ALT_RANGE_NO
1	01-JAN-24 09.00.02.000000 AM	0	1
3	01-JAN-24 09.00.03.000000 AM	0	1
6	01-JAN-24 09.00.04.000000 AM	2	1
8	01-JAN-24 09.00.09.000000 AM	11	2
14	01-JAN-24 09.00.10.000000 AM	15	2
11	01-JAN-24 09.00.22.000000 AM	22	2
12	01-JAN-24 09.00.28.000000 AM	4	3
10	01-JAN-24 09.00.32.000000 AM	0	3
15	01-JAN-24 09.00.33.000000 AM	2	3
16	01-JAN-24 09.00.34.000000 AM	8	4
17	01-JAN-24 09.00.35.000000 AM	12	4
18	01-JAN-24 09.00.38.000000 AM	3	5