I have a table with below structure:
create table DOC_AMOUNT_DETAIL
(
col_id NUMBER,
amount NUMBER,
operation_date DATE
)
and some example data for this table is :
col_id | amount | operation_date
---------------------------------------
1 | 5000 | 11/1/2020
2 | 1000 | 11/1/2020
3 | 3000 | 11/1/2020
1 | 1000 | 11/14/2020
2 | 500 | 11/14/2020
I'm trying to use last_value
function to have below output:
col_id | amount
--------------------
1 | 1000
2 | 500
3 | 3000
and the problem is that the query does not give me the desired result :
select t.col_id,
last_value(t.amount) over(partition by t.col_id order by t.operation_date
) amount
from doc_amount_detail t
where t.operation_date <= to_date('11/15/2020', 'mm/dd/yyyy');
Instead, it gives me this :
col_id | amount
--------------------
1 | 5000
1 | 1000
2 | 1000
2 | 500
3 | 3000
Everything seems fine with my query so can anybody please explain where the exact problem is?
Thanks in advance
Best Answer
Explaination
The
ORDER BY
clause (in an analytical function), implies a window clause ofROWS 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.Example
Results: