Skip to main content

Categories: Window

FIRST_VALUE

Returns the first value within an ordered group of a result set.

Syntax​

FIRST_VALUE(expression VARCHAR, order_subclause VARCHAR) → VARCHAR​

  • expression: The expression that determines the return value.
  • order_subclause: A subclause that specifies the order of the rows within each partition of the result set.

Examples

FIRST_VALUE example 
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
-- ...
FIRST_VALUE example: Window function with cumulative window frame 
SELECT city, state, pop, FIRST_VALUE(pop)
  OVER (PARTITION BY state ORDER BY city RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM Samples."samples.dremio.com"."zips.json";
-- city, state, pop, EXPR$3
-- 98791, AK, 5345, 5345
-- AKHIOK, AK, 13309, 13309
-- AKIACHAK, AK, 481, 481
-- ...
FIRST_VALUE example: Window function with sliding window frame 
SELECT city, state, pop, FIRST_VALUE(pop)
  OVER (PARTITION BY state ORDER BY city ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
FROM Samples."samples.dremio.com"."zips.json";
-- city, state, pop, EXPR$3
-- 98791, AK, 5345, 5345
-- AKHIOK, AK, 13309, 5345
-- AKIACHAK, AK, 481, 13309
-- ...

Usage Notes​

The FIRST_VALUE function supports optional PARTITION BY and cumulative and sliding window frame subclauses. See Window Functions for more information and syntax.