To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: row number with in cursor
Date: Fri, 7 Oct 2016 07:42:32 +0200
Lines: 24
Message-ID: <nt7cjs$c4l$1@blaine.gmane.org>
References: 
 <CAHCyeW1VwGD5JYX4GhHj-Yr3Tmupnrb_onm2BX+Vopb84=Tf1w@mail.gmail.com>
 <81027780-1d91-4d1a-a1ef-7a6cfc77b37b@aklaver.com>
 <CAHCyeW07Z8eVfGVnVx-GmP5cM8OJ9ghyd8Q0ShxP2P9jhTsUYg@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23)
 Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
In-Reply-To: 
 <CAHCyeW07Z8eVfGVnVx-GmP5cM8OJ9ghyd8Q0ShxP2P9jhTsUYg@mail.gmail.com>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql