Skip to main content
Version: current [26.x]

Categories: Window

LAST_VALUE

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

Syntax​

LAST_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

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