Skip to main content

NTH_VALUE

Returns the value of the expression evaluated against the Nth 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 row N of the window frame. Rows are numbered within the window frame starting from 1 in the order defined by the ORDER BY clause if present, or in arbitrary order otherwise. If there is no Nth row in the partition, then NULL is returned.

See also: [SELECT] [FIRST_VALUE] [LAST_VALUE]

Syntax​

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

Arguments​

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

N A positive integer specifying which row number to retrieve (1-indexed).

PARTITION BY column Optional. Divides the result set into partitions. The NTH_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.

Return Value(s)​

Returns the value of the expression evaluated against the Nth row in the window frame, or NULL if the Nth row does not exist.

Examples​

Get the second-highest contributor​

-- Get the top 3 contributors by name for comparison
SELECT
    login,
    contributions,
    NTH_VALUE(login, 1) OVER (ORDER BY contributions DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as top_contributor,
    NTH_VALUE(login, 2) OVER (ORDER BY contributions DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as second_contributor,
    NTH_VALUE(login, 3) OVER (ORDER BY contributions DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as third_contributor
FROM github.repos.contributors
WHERE owner = 'stackql'
  AND repo = 'stackql';

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