agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: row number with in cursor
Date: Fri, 7 Oct 2016 07:42:32 +0200
Message-ID: <nt7cjs$c4l$1@blaine.gmane.org> (raw)
In-Reply-To: <CAHCyeW07Z8eVfGVnVx-GmP5cM8OJ9ghyd8Q0ShxP2P9jhTsUYg@mail.gmail.com>
References: <CAHCyeW1VwGD5JYX4GhHj-Yr3Tmupnrb_onm2BX+Vopb84=Tf1w@mail.gmail.com>
<81027780-1d91-4d1a-a1ef-7a6cfc77b37b@aklaver.com>
<CAHCyeW07Z8eVfGVnVx-GmP5cM8OJ9ghyd8Q0ShxP2P9jhTsUYg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
view thread (5+ messages)
Message-ID: <nt7cjs$c4l$1@blaine.gmane.org>
Permalink: ../nt7cjs$c4l$1@blaine.gmane.org/
Also on: postgresql.org/message-id/nt7cjs$c4l$1@blaine.gmane.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: row number with in cursor
In-Reply-To: <nt7cjs$c4l$1@blaine.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox