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:[email protected]] 
Envoyé : jeudi 25 janvier 2018 19:44
À : Olivier Leprêtre <[email protected]>
Cc : [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.