Re: nth_value and row_number in a partition
| От | David G. Johnston |
|---|---|
| Тема | Re: nth_value and row_number in a partition |
| Дата | |
| Msg-id | CAKFQuwbB-74cLV1eDZFeqSEjVzAR5QKiUKAnJrYRCoLtM2cDow@mail.gmail.com обсуждение исходный текст |
| Ответ на | RE: nth_value and row_number in a partition (Olivier Leprêtre <o.lepretre@gmail.com>) |
| Список | pgsql-sql |
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.
В списке pgsql-sql по дате отправления: