0

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

  • Four digit version of DB? Also, INSERT statements would be nice. – Michael Kutz Nov 15 '20 at 13:53
3

Explaination

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.

Example

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;

Results:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.