FIRST_VALUE

FIRST_VALUE is a window function that returns the values of a specified expression that is evaluated at the first row of a window for the current row. This means that you can select a first value from a set of rows without having to use a self join.

Syntax

FIRST_VALUE ( expression [ {IGNORE | RESPECT} NULLS ] ) OVER ( overClause )

expression

The expression to evaluate; typically a column name or computation involving a column name.

IGNORE NULLS

If this optional qualifier is specified, NULL values are ignored, and the first non-NULL value is evaluated.

If you specify this and all values are NULL, FIRST_VALUE returns NULL.

RESPECT NULLS

This qualifier is the default behavior: it specifies that the first value is always returned, even if it is NULL.

overClause

See the  OVER clause documentation.

Usage Notes

Splice Machine recommends that you use the FIRST_VALUE function with the  ORDER BY clause to produce deterministic results.

Results

Returns value(s) resulting from the evaluation of the specified expression; the return type is of the same value type as the date stored in the column used in the expression..

  • FIRST_VALUE returns the first value in the set, unless that value is NULL and you have specified the IGNORE NULLS qualifier; if you’ve specified IGNORE NULLS, this function returns the first non-NULL value in the set.
  • If all values in the set are NULL, FIRST_VALUE always returns NULL.

Splice Machine always sorts NULL values first in the results.

Examples

The following query finds all players with 10 or more HomeRuns, and compares each player’s home run count with the lowest total within that group on his team:

splice> SELECT Team, DisplayName, HomeRuns,
   FIRST_VALUE(HomeRuns) OVER (PARTITION BY Team ORDER BY HomeRuns
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "Least"
   FROM Players JOIN Batting ON Players.ID=Batting.ID
   WHERE HomeRuns > 10
   ORDER BY Team, HomeRuns DESC;
TEAM     |DISPLAYNAME             |HOMER&|Least
-----------------------------------------------
Cards    |Mitch Canepa            |28    |11
Cards    |Jonathan Pearlman       |17    |11
Cards    |Roger Green             |17    |11
Cards    |Michael Rastono         |13    |11
Cards    |Jack Hellman            |13    |11
Cards    |Kelly Wacherman         |11    |11
Giants   |Bob Cranker             |21    |12
Giants   |Buddy Painter           |19    |12
Giants   |Billy Bopper            |18    |12
Giants   |Mitch Duffer            |12    |12

10 rows selected

See Also