Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query
optimization, and usability that you won’t want to miss.
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.
NTH_VALUE ( expression, n ) OVER (
[PARTITION BY (col | expr), ...]
[ORDER BY (col | expr), ...]
[frame_clause]
)
The result type of expression
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 |
+------+-----------+