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 first row of the window frame. If there are no rows in the frame, returns NULL
.
For an overview of window functions in SingleStore DB, see Window Functions Guide.
FIRST_VALUE ( expression ) 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, first_value(a) over(order by (a)) from example;
+------+-----------------------------------+
| a | first_value(a) over(order by (a)) |
+------+-----------------------------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
+------+-----------------------------------+
select a, first_value(a) over(order by (a) rows between current row and unbounded following) from example;
+------+------------------------------------------------------------------------------------+
| a | first_value(a) over(order by (a) rows between current row and unbounded following) |
+------+------------------------------------------------------------------------------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+------+------------------------------------------------------------------------------------+