DB Software v7.8

NTH_VALUE

Value function. 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 SingleStore DB, 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 example (a int, b int);

insert into example values (1, 2), (2, 2), (3, 3), (4, 3);

select a, nth_value(a, 2) over (order by a) from 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 example;
+------+-----------+
| a    | nth_value |
+------+-----------+
|    1 |         2 |
|    2 |         2 |
|    3 |         1 |
|    4 |         1 |
+------+-----------+
In this section: