select 100, 1, 2022, 'APPROVED', null, 100.1 from dual union all

select 101, 1, 2022, 'APPROVED', 0, 100.1 from dual union all

select 102, 1, 2022, 'APPROVED', 0, 200.6 from dual union all

select 103, 1, 2022, 'APPROVED', 0, 199.3 from dual union all

select 104, 1, 2022, 'APPROVED', 0, 201 from dual union all

select 105, 2, 2023, 'PROPOSED', 0, 50 from dual union all

select 106, 2, 2023, 'PROPOSED', 75, 100 from dual union all

select 107, 3, 2024, 'DEFERRED', 0, 100 from dual union all

select 108, 3, 2025, 'DEFERRED', 0, 110 from dual union all

select 109, 4, 2026, 'PROPOSED', 0, null from dual union all

select 110, 4, 2026, 'DEFERRED', 0, null from dual

where exists (

select null

from road_projects rp2

where rp2.proj_id != rp.proj_id

and rp2.road_id = rp.road_id

and rp2.year_ = rp.year_

and rp2.status = rp.status

and abs(coalesce(rp2.from_measure, 0) - coalesce(rp.from_measure, 0)) < 5

and abs(coalesce(rp2.to_measure, 0) - coalesce(rp.to_measure, 0)) < 5

)

order by proj_id

from_tolerance, to_tolerance,

dupe_group, first_proj_id, first_from_measure, last_to_measure

from road_projects

match_recognize (

partition by road_id, year_, status

order by from_measure nulls first, to_measure

measures

match_number() as dupe_group,

first(proj_id) as first_proj_id,

first(from_measure) as first_from_measure,

final last(to_measure) as last_to_measure,

abs(coalesce(dupe.from_measure, 0) - coalesce(prev(dupe.from_measure), 0)) as from_tolerance,

abs(coalesce(dupe.to_measure, 0) - coalesce(prev(dupe.to_measure), 0)) as to_tolerance

all rows per match

after match skip past last row

pattern (orig dupe+)

define

dupe as abs(coalesce(dupe.from_measure, 0) - coalesce(prev(dupe.from_measure), 0)) < 5

and abs(coalesce(dupe.to_measure, 0) - coalesce(prev(dupe.to_measure), 0)) < 5

)

order by proj_id