Re: nth_value and row_number in a partition

On Thu, Jan 25, 2018 at 12:49 PM, Olivier Leprêtre <o(dot)lepretre(at)gmail(dot)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.

David J.