Re: Fastest way of getting value associated with max(...)

"FC" <flavio_at_tin.it> wrote in message
news:2bd78ddf.0402250153.6ccdaf6_at_posting.google.com...
> SELECT max(to_char(exch_date,'YYYYMMDD') || '-' || to_char(exch_rate))
> INTO mystr
> FROM exchange
> WHERE curr_from = :new.curr_from_code
> AND curr_to = :new.curr_to_code
> AND exch_date <= sysdate;
>

> The best alternative way I found so far is:
>
> select exch_rate
> from exchange
> where curr_from = :new.curr_from_code
> and curr_to = :new.curr_to_code
> and exch_date = (select max(exch_date)
> from exchange
> where curr_from = :new.curr_from_code
> and curr_to = :new.curr_to_code
> and exch_date <= sysdate);

If its Oracle 9i then this could be a possibility :

select max(exch_rate) keep (dense_rank last order by exch_date) from exchange
where curr_from = :new.curr_from_code
and curr_to = :new.curr_to_code
and exch_date <= sysdate;

That means that for those records having the last exch_date, select the max of exch_rate.
As presumably there is only one record for each exch_date, that should be OK.
In case you have ties with same exch_date, the original logic would fail too.

For Oracle 8i analytics could be used like this :

select last_exch_rate from (
  select last_value(exch_rate) over (order by exch_date rows between unbounded preceding and unbounded following) last_exch_rate   from exchange
  where curr_from = :new.curr_from_code
  and curr_to = :new.curr_to_code
  and exch_date <= sysdate
) where rownum = 1;

Hope those may be better ? But not sure... Try them out and see :-)

/KiBeHa Received on Wed Feb 25 2004 - 06:23:45 CST