0

I have a table with below structure:

create table DOC_AMOUNT_DETAIL
(
  col_id         NUMBER,
  amount         NUMBER,
  operation_date DATE      
)

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

If the amount column of each col_id changes , a new record will be inserted in the table with a new operation_date which is exactly the date of the change. What I need from the table is the amount for each col_id which has the max(operation_date)<= (a specific date for example 11/15/2020) by this I mean I need the last update of the record the operation_date of which is lower than the date I'm querying the table. So my desired result is this :

    col_id    |  amount   
    --------------------
      1       |  1000      
      2       |  500    
      3       |  3000       

What I've written so far is this and it gives me the correct output:

select d.col_id, 
       d.amount
  from doc_amount_detail d
 inner join (select t.col_id, 
             max(t.operation_date) operation_date
               from doc_amount_detail t
              where t.operation_date <= to_date('11/15/2020', 'mm/dd/yyyy')
              group by t.col_id
             ) d2
    on d.col_id = d2.col_id
   and d.operation_date = d2.operation_date

and I want to know whether there are better ways of writing this query. Thanks in advance.

4
  • 1
    LAST_VALUE would be one way. Nov 15, 2020 at 12:48
  • @MichaelKutz This one gives me the wrong 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');
    – Pantea
    Nov 15, 2020 at 12:57
  • 1
    I believe this falls under "Top N" query category. I'm sure there is a Wiki/FAQ on here for Oracle. I was requested to add MATCH RECOGNIZE to potential solutions but lost the link. (Haven't found it yet) Nov 15, 2020 at 14:30
  • @MichaelKutz , I used first_value function instead of last_value which I think is a bit tricky and at the same time a bit scary !
    – Pantea
    Nov 15, 2020 at 14:48

2 Answers 2

1

Analytic Functions

select
  col_id, amount
from
 (
   select
     d.col_id, d.amount,
     rank() over (partition by d.col_id order by d.operation_date desc) as rk
   from doc_amount_detail d
   where.d.operation_date <= to_date('11/15/2020', 'mm/dd/yyyy')
 )
where rk = 1; 
0

I found a better solution. Instead of Last_value() function , I used First_value() and order by t.operation_date desc:

select distinct 
       t.col_id,
       first_value(t.amount) over(partition by t.col_id order by t.operation_date desc ) amount
from doc_amount_detail t
where t.operation_date <= to_date('11/15/2020', 'mm/dd/yyyy');

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.