NTH_VALUE OVER

​

Syntax

NTH_VALUE( <expression>, <n> ) OVER ( [ PARTITION BY <partition_by> ] ORDER BY <order_by> [ASC|DESC] [ <frame_clause> ] )

​

Parameters

​

Return Type

​

Remarks

• <n> must be greater than or equal to 1. Passing 0 or a negative value raises an error.
• Returns NULL when N exceeds the number of rows in the window frame.
• This function respects NULL values, and results will be ordered with default null ordering NULLS LAST unless otherwise specified in the ORDER BY clause.

​

Example

SELECT
    level,
    leveltype,
    maxpoints,
    NTH_VALUE(maxpoints, 2) OVER (
        PARTITION BY leveltype
        ORDER BY maxpoints
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_lowest
FROM levels
ORDER BY leveltype, maxpoints;