clear markdown help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601504 fiddles created (47963 in the last week).

with units(org_id, title, unit_valid_from, unit_valid_to, origin) as ( select 101, 'T101A', date '2019-05-14', date '2019-05-17', 'different' from dual union all select 101, 'T101B', date '2019-05-15', date '2019-05-17', 'history' from dual union all select 101, 'T101C', date '2019-05-16', date '2019-05-17', 'current' from dual union all select 201, 'T201A', date '2019-05-14', date '2019-05-17', 'different' from dual union all select 201, 'T201B', date '2019-05-15', date '2019-05-17', 'history' from dual union all select 301, 'T301A', date '2019-05-14', date '2019-05-17', 'different' from dual union all select 301, 'T301B', date '2019-05-15', date '2019-05-17', 'different' from dual ) select units.*, case when 1 = row_number() over ( partition by org_id order by case origin when 'current' then 1 when 'history' then 2 else 3 end, unit_valid_from ) then 1 else 0 end as is_correct_version from units
ORG_ID TITLE UNIT_VALID_FROM UNIT_VALID_TO ORIGIN IS_CORRECT_VERSION
101 T101C 16-MAY-19 17-MAY-19 current 1
101 T101B 15-MAY-19 17-MAY-19 history 0
101 T101A 14-MAY-19 17-MAY-19 different 0
201 T201B 15-MAY-19 17-MAY-19 history 1
201 T201A 14-MAY-19 17-MAY-19 different 0
301 T301A 14-MAY-19 17-MAY-19 different 1
301 T301B 15-MAY-19 17-MAY-19 different 0
ORG_ID TITLE UNIT_VALID_FROM UNIT_VALID_TO ORIGIN IS_CORRECT_VERSION
101 T101C 16-MAY-19 17-MAY-19 current 1
101 T101B 15-MAY-19 17-MAY-19 history 0
101 T101A 14-MAY-19 17-MAY-19 different 0
201 T201B 15-MAY-19 17-MAY-19 history 1
201 T201A 14-MAY-19 17-MAY-19 different 0
301 T301A 14-MAY-19 17-MAY-19 different 1
301 T301B 15-MAY-19 17-MAY-19 different 0
 hidden batch(es)