oracle – PL-SQL “LAST_VALUE” function does not give the desired result

The ORDER BY clause (in an analytical function), implies a window clause of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

You need ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Since this will apply to each row, you’ll need a DISTINCT clause.

with data(col_id, amount, operation_date ) as (
    select  1,  5000,  to_date( '11/1/2020', 'mm/dd/yyyy' ) from dual union all
    select  2,  1000,  to_date( '11/1/2020', 'mm/dd/yyyy' ) from dual union all 
    select  3,  3000,  to_date( '11/1/2020', 'mm/dd/yyyy' ) from dual union all
    select  1,  1000,  to_date( '11/14/2020', 'mm/dd/yyyy' ) from dual union all
    select  2,   500,  to_date( '11/14/2020', 'mm/dd/yyyy' ) from dual
)
select distinct col_id
    , last_value( amount ) over (partition by col_id order by operation_date
                                 rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) amt
from data;

enter image description here