Skip to main content

LAST_VALUE

Returns the value of the expression evaluated against the last row in the window frame.

This built-in window function calculates the window frame for each row in the same way as an aggregate window function. It returns the value of the expression evaluated against the last row in the window frame for each row.

See also: [SELECT] [FIRST_VALUE] [NTH_VALUE]

Syntax​

SELECT LAST_VALUE(expr) OVER (
    [PARTITION BY column]
    ORDER BY column
    [ROWS BETWEEN frame_start AND frame_end]
) FROM <multipartIdentifier>;

Arguments​

expr The expression to evaluate against the last row in the window frame.

PARTITION BY column Optional. Divides the result set into partitions. The LAST_VALUE function is applied within each partition separately.

ORDER BY column Specifies the order in which rows are processed.

ROWS BETWEEN frame_start AND frame_end Optional. Defines the window frame. Common options include:

  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (default)
  • ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Return Value(s)​

Returns the value of the expression evaluated against the last row in the window frame.

Examples​

Find the latest release for context​

-- Find first and latest release for context
SELECT
    tag_name,
    name,
    published_at,
    FIRST_VALUE(tag_name) OVER (
        ORDER BY published_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as first_release,
    LAST_VALUE(tag_name) OVER (
        ORDER BY published_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as latest_release
FROM github.repos.releases
WHERE owner = 'stackql'
  AND repo = 'stackql';
tip

By default, the window frame extends from the start of the partition to the current row, which means LAST_VALUE would return the current row's value. To get the actual last value in the partition, use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

For more information, see https://sqlite.org/windowfunctions.html#built-in_window_functions.