Outdated Version
You are viewing an older version of this section. View current production version.
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 |
+------+-----------+
