Thank you for such a quick response.
Why did last_value return ALL records to me and not one.
first_value(rg.spath) OVER (PARTITION BY rg.id_path ORDER BY rg.level DESC) AS first_value
last_value(rg.spath) OVER (PARTITION BY rg.id_path ORDER BY rg.level) AS last_value
last_value returns all rows, first_value one.? I don't understand this behavior.
id_path | spath
---------+------------------------------------------------------
386 | group_root\group_level_1\group_level_2\group_level_3
386 | group_level_3
386 | group_level_2\group_level_3
386 | group_level_1\group_level_2\group_level_3
Thank you for your time.
Дмитрий Иванов <firstdismay@gmail.com> writes:
> I cannot understand the behavior of the last_value function. Conditions
> opposite to first_value conditions. Why such a result?
The default window frame isn't symmetrical is why. Per the manual [1]:
Note that first_value, last_value, and nth_value consider only the
rows within the “window frame”, which by default contains the rows
from the start of the partition through the last peer of the current
row. This is likely to give unhelpful results for last_value and
sometimes also nth_value. You can redefine the frame by adding a
suitable frame specification (RANGE, ROWS or GROUPS) to the OVER
clause. See Section 4.2.8 for more information about frame
specifications.
That is, if you do nothing to change it then first_value returns the
value at the first row of the partition, while last_value returns the
value at the last peer of the current row (which isn't even very
well-defined when the current row has some peers).
regards, tom lane
[1] https://www.postgresql.org/docs/current/functions-window.html