Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER


Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE	11.2.0.2.0	Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production


alter session set events '22829 trace name context forever';
create table t1 (id, name) as 
    select rownum, 'item '||rownum from dual connect by level<=3
3 rows affected

create table t2 (id, tid, text, created) as 
    select rownum, t1.id, 'text '||rownum, date'2021-08-01'+(0.3*rownum)  
    from t1, (select rownum rn from dual connect by level<=3)
9 rows affected

select a.*, b.text, b.created
from t1 a, lateral (
    select 
        max(text)    keep (dense_rank first order by created desc) text,     
        max(created) keep (dense_rank first order by created desc) created     
    from t2 b 
    where b.tid = a.id)(+) b


ID
NAME
TEXT
CREATED


1
item 1
text 7
03-AUG-21

2
item 2
text 8
03-AUG-21

3
item 3
text 9
03-AUG-21

ID	NAME	TEXT	CREATED
1	item 1	text 7	03-AUG-21
2	item 2	text 8	03-AUG-21
3	item 3	text 9	03-AUG-21