Categories: Window
FIRST_VALUE
Returns the first value within an ordered group of a result set.
Syntax
FIRST_VALUE(expression) OVER ( [PARTITION BY partition_expression] [ORDER BY order_expression] [ (ASC | DESC) ] ) → same as input type
- expression: The expression that determines the return value.
- partition_expression: An optional expression that groups rows into partitions. You can specify a single expression or a comma-separated list of expressions. For example,
PARTITION BY column1, column3, …
- order_expression: An expression that specifies the order of the rows within each partition. You can specify a single expression or a comma-separated list of expressions. For example,
PARTITION BY column1, column3, …
Examples
SELECT city, state, pop,
FIRST_VALUE(pop) OVER (PARTITION BY state ORDER BY city)
FROM Samples."samples.dremio.com"."zips.json"
-- city, state, pop, EXPR$3
-- 98791, AK, 5345, 5345
-- AKHIOK, AK, 13309, 5345
-- AKIACHAK, AK, 481, 5345
-- ...
Usage Notes
The example uses Dremio’s Samples Data Lake, which includes sample datasets that you can practice with. You must manually add this data lake. To add in Dremio Cloud, see Add Dremio’s Samples Data Lake to Your Project. Guidance to add in Dremio Software is in development.
Was this page helpful?
Glad to hear it! Thank you for your feedback.
Sorry to hear that. Thank you for your feedback.