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 nls_date_format='yyyy-mm-dd hh24:mi'
with mt_operation_history (id, operation_datetime) as (
    select mod(rownum,2)+1, date'2020-03-23'+rownum/24
    from xmlTable ('1 to 6')
)
select *  
from mt_operation_history
order by id, operation_datetime;


ID
OPERATION_DATETIME


1
2020-03-23 02:00

1
2020-03-23 04:00

1
2020-03-23 06:00

2
2020-03-23 01:00

2
2020-03-23 03:00

2
2020-03-23 05:00


with mt_operation (id, name) as (
    select rownum, 'oper '||rownum 
    from xmlTable ('1 to 2')
),
mt_operation_history (id, operation_datetime) as (
    select mod(rownum,2)+1, date'2020-03-23'+rownum/24
    from xmlTable ('1 to 6')
)
select mop.id, mop.name, moh.lastDT
from mt_operation mop
join (
    select 
        id, max (operation_datetime) keep (dense_rank last order by operation_datetime) lastDT  
    from mt_operation_history
    group by id) moh on moh.id = mop.id 
order by id;


ID
NAME
LASTDT


1
oper 1
2020-03-23 06:00

2
oper 2
2020-03-23 05:00

ID	OPERATION_DATETIME
1	2020-03-23 02:00
1	2020-03-23 04:00
1	2020-03-23 06:00
2	2020-03-23 01:00
2	2020-03-23 03:00
2	2020-03-23 05:00