On Thu, Jan 25, 2018 at 12:49 PM, Olivier Leprêtre <o.lepretre@gmail.com> wrote:
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
I take it "imbricated" is the word for "nested" in a different language...
WITH vals AS ( VALUES (1), (2), (3) )
SELECT max( row_number() OVER () ) OVER ()
FROM vals;
Results in:
SQL Error: ERROR: window function calls cannot be nested
LINE 2: SELECT max( row_number() OVER () ) OVER ()
Which means that what you are trying to do (or at least they way you are trying to do it) cannot be done.
You will need to resort to a subquery to compute the row_number for each row and then feed that into the upper query as column reference.