Hi,
I can't find the right syntax to use simultaneously nth_value and row_number in a partition.
I have a table with roads and their orders, a road segment name and two values v1 and v2
I want to decrement first_value v1 of each partition with the current record value v2 of the partition in a third column v3 in the same record. So I thought that I could combine and row_number which returns "number of the current row within its partition, counting from 1" with nth_value
My problem is that I get either syntax error near over, window function needs an over clause, nth_value(integer, bigint) doesn't exists. Query works if I just uses something like
nth_value(v2,1) but I can't find how I can replace "1" with the current row position in the partition (1,2,3…)
I tried dozen of syntax with no success. Here is one of them
select roads,orders,(first_value(v1) over (partition by roads,segment order by orders)-(nth_value(v2,(cast(row_number() as integer) over (partition by roads,segments order by orders))) over (partition by roads,segments order by orders))) as result from my table
Thanks for any help !
Olivier