Paste this into a new question or an answer at dba.stackexchange.com:
<!-- --> > 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 > > <pre> > 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 > </pre> *db<>fiddle [here](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=799bc4b9e1958e474da1b812b86e3db2)*
back to fiddle