Re: nth_value and row_number in a partition

same to you
it's always a pleasure to help

Am 26.01.2018 um 17:56 schrieb Olivier Leprêtre:
>
> David, Martin, thanks for your kind help everything works now !
>
> Nice week-end to all of you
>
> Olivier
>
> *De :*Martin Stöcker [mailto:[email protected]]
> *Envoyé :* jeudi 25 janvier 2018 21:13
> *À :* Olivier Leprêtre <[email protected]>; 
> [email protected]
> *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:[email protected]]
>     *Envoyé :* jeudi 25 janvier 2018 19:44
>     *À :* Olivier Leprêtre <[email protected]>
>     <mailto:[email protected]>
>     *Cc :* [email protected]
>     <mailto:[email protected]>
>     *Objet :* Re: nth_value and row_number in a partition
>
>     On Thursday, January 25, 2018, Olivier Leprêtre
>     <[email protected] <mailto:[email protected]>> 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.
>