Question :
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.
Answer :
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;
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');