NTH_VALUE
Returns an expression evaluated at the nth row of the window frame.
If there are fewer than n rows in the frame, returns NULL
. For an overview of window functions in SingleStoreDB, see Window Functions Guide.
Syntax
NTH_VALUE ( expression, n ) OVER ( [PARTITION BY (col | expr), ...] [ORDER BY (col | expr), ...] [frame_clause] )
Arguments
expression: any expression. This may be a column name, the result of another function, or a math operation.
n: an integer or integer expression.
Return Type
The result type of expression
Examples
CREATE TABLE nth_example (a int, b int); INSERT INTO nth_example VALUES (1, 2), (2, 2), (3, 3), (4, 3);
SELECT a, NTH_VALUE(a, 2) OVER (ORDER BY a) FROM nth_example; **** +------+-----------------------------------+ | a | NTH_VALUE(a, 2) OVER (ORDER BY a) | +------+-----------------------------------+ | 1 | NULL | | 2 | 2 | | 3 | 2 | | 4 | 2 | +------+-----------------------------------+
SELECT a, NTH_VALUE(a, case when a > 2 then 1 else 2 end) OVER (ORDER BY a rows between unbounded preceding and unbounded following) as nth_value FROM nth_example; **** +------+-----------+ | a | nth_value | +------+-----------+ | 1 | 2 | | 2 | 2 | | 3 | 1 | | 4 | 1 | +------+-----------+