By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release | Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0 |
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release | 0 |
create table table1 (ID int, TABLE1_VER int, AUDIT_DATETIME date);
begin
Insert into TABLE1 (ID,TABLE1_VER,AUDIT_DATETIME) values ('0002073263',1,to_date('10/30/2023 09:57:05','MM/DD/YYYY HH24:MI:SS'));
Insert into TABLE1 (ID,TABLE1_VER,AUDIT_DATETIME) values ('0002073263',2,to_date('10/30/2023 09:57:06','MM/DD/YYYY HH24:MI:SS'));
Insert into TABLE1 (ID,TABLE1_VER,AUDIT_DATETIME) values ('0002073263',3,to_date('10/30/2023 09:57:34','MM/DD/YYYY HH24:MI:SS'));
end;
/
1 rows affected
create table table2 (ID int, TABLE2_VER int, AUDIT_DATETIME date, ASSOCIATED_VERSION_TABLE1 int);
begin
Insert into TABLE2 (ID,TABLE2_VER,AUDIT_DATETIME,ASSOCIATED_VERSION_TABLE1) values ('0002073263',1,to_date('10/30/2023 09:57:05','MM/DD/YYYY HH24:MI:SS'),1);
Insert into TABLE2 (ID,TABLE2_VER,AUDIT_DATETIME,ASSOCIATED_VERSION_TABLE1) values ('0002073263',2,to_date('10/30/2023 10:55:04','MM/DD/YYYY HH24:MI:SS'),3);
Insert into TABLE2 (ID,TABLE2_VER,AUDIT_DATETIME,ASSOCIATED_VERSION_TABLE1) values ('0002073263',3,to_date('10/30/2023 10:55:22','MM/DD/YYYY HH24:MI:SS'),3);
end;
/
1 rows affected
select
t1.table1_ver,
to_char(t1.audit_datetime, 'yyyy-mm-dd hh24:mi:ss') as t1_adt,
t2.table2_ver,
to_char(t2.audit_datetime, 'yyyy-mm-dd hh24:mi:ss') as t2_adt,
t1.table1_ver + t2.table2_ver as combined_ver
from table1 t1
cross apply
(
select *
from table2 t2
where t2.id = t1.id
and t2.audit_datetime <= t1.audit_datetime
order by t2.audit_datetime desc
fetch first row only
) t2
union
select
t1.table1_ver,
to_char(t1.audit_datetime, 'yyyy-mm-dd hh24:mi:ss') as t1_adt,
t2.table2_ver,
to_char(t2.audit_datetime, 'yyyy-mm-dd hh24:mi:ss') as t2_adt,
t1.table1_ver + t2.table2_ver as combined_ver
from table2 t2
cross apply
(
select *
from table1 t1
where t1.id = t2.id
and t1.audit_datetime <= t2.audit_datetime
order by t1.audit_datetime desc
fetch first row only
) t1
order by combined_ver, table1_ver, table2_ver;
| TABLE1_VER | T1_ADT | TABLE2_VER | T2_ADT | COMBINED_VER |
|---|---|---|---|---|
| 1 | 2023-10-30 09:57:05 | 1 | 2023-10-30 09:57:05 | 2 |
| 2 | 2023-10-30 09:57:06 | 1 | 2023-10-30 09:57:05 | 3 |
| 3 | 2023-10-30 09:57:34 | 1 | 2023-10-30 09:57:05 | 4 |
| 3 | 2023-10-30 09:57:34 | 2 | 2023-10-30 10:55:04 | 5 |
| 3 | 2023-10-30 09:57:34 | 3 | 2023-10-30 10:55:22 | 6 |