RE: nth_value and row_number in a partition

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(dot)g(dot)johnston(at)gmail(dot)com]
Envoyé : jeudi 25 janvier 2018 19:44
À : Olivier Leprêtre <o(dot)lepretre(at)gmail(dot)com>
Cc : pgsql-sql(at)lists(dot)postgresql(dot)org
Objet : Re: nth_value and row_number in a partition

On Thursday, January 25, 2018, Olivier Leprêtre <o(dot)lepretre(at)gmail(dot)com <mailto:o(dot)lepretre(at)gmail(dot)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.