LAST_VALUE() OVER ...

All functions > WINDOW FUNCTION > LAST_VALUE() OVER ...

Returns the last value in the window frame.

Syntax

LAST_VALUE(expr) OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

LAST_VALUE(expr) OVER ([PARTITION BY expr [, ...]] [ORDER BY sort_item [, ...]] [ROWS|RANGE|GROUPS frame])

Notes

• Returns the value from the last row of the window frame as defined by ORDER BY and the frame
• With the default frame ending at the current row, LAST_VALUE is usually the current row’s value, not the last row of the whole partition
• Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (or another explicit frame) when you need the partition’s true last value
• Returns same type as input expression
• NULL values are preserved

See also

• OVER clause

Examples

SELECT
    f1 := ZIP(ARRAY[1, 2, 3] AS id, ARRAY[30, 10, 20] AS v).TRANSFORM(SELECT LAST_VALUE(v) OVER (ORDER BY id ASC)).UNWRAP() -- Default frame ends at current row, so LAST_VALUE is current-row v (V is permuted so this is not ascending order)
;