agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Olivier Leprêtre <olepretre@maule.fr>
To: 'Martin Stöcker' <martin.stoecker@stb-datenservice.de>
To: pgsql-sql@lists.postgresql.org
To: 'David G. Johnston' <david.g.johnston@gmail.com>
Subject: RE: nth_value and row_number in a partition
Date: Fri, 26 Jan 2018 17:56:04 +0100
Message-ID: <004e01d396c6$907e6590$b17b30b0$@maule.fr> (raw)
In-Reply-To: <ebb0e64d-ab2a-cd82-c33f-c85550312d74@stb-datenservice.de>
References: <015201d3960a$a2db0b60$e8912220$@gmail.com>
<CAKFQuwa3SCRhpnjOK0=dNxL=vt2Lvrs9VYLhJ8p2UZrUqieShQ@mail.gmail.com>
<016401d39615$9b620bd0$d2262370$@gmail.com>
<ebb0e64d-ab2a-cd82-c33f-c85550312d74@stb-datenservice.de>
David, Martin, thanks for your kind help everything works now !
Nice week-end to all of you
Olivier
De : Martin Stöcker [mailto:martin.stoecker@stb-datenservice.de]
Envoyé : jeudi 25 janvier 2018 21:13
À : Olivier Leprêtre <o.lepretre@gmail.com>; pgsql-sql@lists.postgresql.org
Objet : Re: nth_value and row_number in a partition
Hi Olivier
can you please give me the structure of your table, maybee some sample data too.
And please describe in words not in SQL your calculation.
Regards Martin
Am 25.01.2018 um 20:49 schrieb Olivier Leprêtre:
Hi David,
Thanks for your answer, I tried your suggestion as well as many other combinations, no success. Here are some of them. I just don't understand which syntax is required
select roads,orders,(first_value(v1)
over (partition by roads,segment order by orders)-(nth_value(v2,cast(row_number() over (partition by roads,order by orders) as integer)) over (partition by roads,segment order by orders))) as result
from mytable
or
select roads,orders,(first_value(v1)
over (partition by roads,segment order by orders)-(nth_value(v2,row_number() over (partition by roads,order by orders)::integer)) over (partition by roads,segment order by orders))) as result
from mytable
>>syntax error near order (bold)
select roads,orders,(first_value(v1)
over (partition by roads,segment order by orders)-(nth_value(v2,row_number() over (partition by roads)::integer)) over (partition by roads,segment order by orders))) as result
from mytable
>> syntax error near over
select roads,orders,(first_value(v1)
over (partition by roads,segment order by orders)-nth_value(v2,row_number() over (partition by roads)::integer) over (partition by roads,segment order by orders)) as result
from mytable
>> window call cannot be imbricated
select roads,orders,(first_value(v1)
over (partition by roads,segment order by orders)-nth_value(v2,row_number() over (partition by roads,segment order by orders)::integer)) as result
from mytable
>> nth_value requires an over clause
select roads,orders,(first_value(v1)
over (partition by roads,segment order by orders)-nth_value(v2,row_number()::integer) over (partition by roads,segment order by orders)) as result
from mytable
>> row_number requires an over clause
De : David G. Johnston [mailto:david.g.johnston@gmail.com]
Envoyé : jeudi 25 janvier 2018 19:44
À : Olivier Leprêtre <mailto:o.lepretre@gmail.com> <o.lepretre@gmail.com>
Cc : pgsql-sql@lists.postgresql.org <mailto:pgsql-sql@lists.postgresql.org>
Objet : Re: nth_value and row_number in a partition
On Thursday, January 25, 2018, Olivier Leprêtre <o.lepretre@gmail.com <mailto:o.lepretre@gmail.com> > wrote:
nth_value(integer, bigint) doesn't exists.
This is close, you just need to cast to integer.
(cast(row_number() as integer) over (partition by roads,segments order by orders)))
You cannot separate the window function from its over clause.
Cast( Row_number() over (...) as integer )
Not tested...and I tend to use :: instead of cast
David J.
view thread (9+ messages) latest in thread
Message-ID: <004e01d396c6$907e6590$b17b30b0$@maule.fr>
Permalink: ../004e01d396c6$907e6590$b17b30b0$@maule.fr/
Also on: postgresql.org/message-id/004e01d396c6$907e6590$b17b30b0$@maule.fr
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: olepretre@maule.fr, martin.stoecker@stb-datenservice.de, pgsql-sql@lists.postgresql.org, david.g.johnston@gmail.com
Subject: RE: nth_value and row_number in a partition
In-Reply-To: <004e01d396c6$907e6590$b17b30b0$@maule.fr>
* 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