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 <o.lepretre@gmail.com>
Cc : 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> 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