Re: row number with in cursor

Kevin Duffy schrieb am 07.10.2016 um 03:01:
> The cursor is defined as follows:
>
> currDR CURSOR (r character(15), sD date ) IS
> select rate_key, rate_date, rate_value,
> LAG(rate_date, 1 , null) OVER w as lag_r_date ,
> LAG(rate_value, 1, null) Over w as lag_r_value ,
> ( LAG(rate_value, 1, null) Over w /100 * ( rate_date - LAG(rate_date, 1 , null) OVER w ) ) / 360 as overnight_rate_return
> from rate_quote
> where rate_key = r and rate_date >= sD
> WINDOW w as (PARTITION BY rate_key order by rate_date ROWS 1 PRECEDING )
> order by 2 ;
>
> Cursor works fine
> Could I add a row number to the above?

Sure, just add:

ROW_NUMBER() OVER W AS RN

As the overall result is ordered by the same expression as the window w, this should work without much overhead.

Thomas