CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
JamesP
JamesP

Reputation: 195

efficient way to get latest data using MAX()

I have a table which stores the exchange rates of currencies on a given day (TBAADM.EXC). However the exchange rates are manually entered, and the erroneous rate entered previously has to be archived, so the table can end up having multiple entries of a given from-to exchange rate per day because of user error or because the exchange rate has changed during the day. To do this, I have a column in the table called UPDATE_NO which indicates that a given entry is the nth entry for the day. For example, the first exchange rate entry for the day will have an UPDATE_NO value of 001. If the exchange rate has changed or was entered incorrectly, the next entry will have a value of 002, and so on.

What I need to do now, is to get the latest entry of a from-to exchange rate for a given day:

select exc_rate
from tbaadm.exc
    where bank_id = 'BANK01'
    and designation = 'REV'
    and fr_crncy_code = 'USD'
    and to_crncy_code = 'PHP'
    and rtlist_date = '10-AUG-2015'
    and update_no = --maximum

This is what I currently have, but I think this would definitely affect the runtime of my script since I traverse the EXC table twice per record:

select exc_rate
    from tbaadm.exc
    where bank_id = 'BANK01'
    and designation = 'REV'
    and fr_crncy_code = 'USD'
    and to_crncy_code = 'PHP'
    and rtlist_date = '10-AUG-2015'
    and update_no = (select max(update_no) from tbaadm.exc
        where bank_id = 'BANK01'
        and designation = 'REV'
        and fr_crncy_code = 'USD'
        and to_crncy_code = 'PHP' 
        and rtlist_date = '10-AUG-2015')

Is there a more efficient way to utilize the MAX() function? Any help will be appreciated.

Upvotes: 1

Views: 168

Answers (3)

SkyWalker
SkyWalker

Reputation: 494

I added some changes to Avrajit Roy's script:

SELECT MAX(exc_rate) KEEP (DENSE_RANK LAST ORDER BY update_no) as needed_rate
FROM tbaadm.exc
WHERE bank_id     = 'BANK01'
AND designation   = 'REV'
AND fr_crncy_code = 'USD'
AND to_crncy_code = 'PHP'
AND rtlist_date   = '10-AUG-2015' ;

If you will need find last rates for all groups, add group by clause

Upvotes: 0

artm
artm

Reputation: 8584

select exc_rate
from (
select exc_rate
    , ROW_NUMBER() OVER (ORDER BY update_no DESC) rnk
    from tbaadm.exc
    where bank_id = 'BANK01'
    and designation = 'REV'
    and fr_crncy_code = 'USD'
    and to_crncy_code = 'PHP'
    and rtlist_date = '10-AUG-2015'

) i
where i.rnk = 1

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm

update:

select exc_rate
from (
select exc_rate
    , fr_crncy_code
    , ROW_NUMBER() OVER (ORDER BY update_no DESC) rnk
    from tbaadm.exc
    where bank_id = 'BANK01'
    and designation = 'REV'
    and to_crncy_code = 'PHP'
    and rtlist_date = '10-AUG-2015'

) i
where i.rnk = 1
    AND fr_crncy_code = 'usd'

Upvotes: 0

Avrajit Roy
Avrajit Roy

Reputation: 3303

Just adding we can also use Analytical functions with KEEP function to restrict the output in the query itself. No need to go via INLINE view by this and performance wise its better. Hope this helps.

SELECT DISTINCT exc_rate,
MAX(update_no) KEEP (DENSE_RANK FIRST
ORDER BY update_no) OVER (PARTITION BY bank_id,designation,fr_crncy_code,to_crncy_code,rtlist_date) "Max dt"
FROM tbaadm.exc
WHERE bank_id     = 'BANK01'
AND designation   = 'REV'
AND fr_crncy_code = 'USD'
AND to_crncy_code = 'PHP'
AND rtlist_date   = '10-AUG-2015' ;

Upvotes: 1

Related Questions