`first` function for MATCH_RECOGNIZE

Applies to: Databricks SQL Databricks Runtime 19.0 and above

Beta

This feature is in Beta. Workspace admins can control access to this feature from the Previews page. See Manage Databricks previews.

Returns a column value relative to the first row in the match. Use this function only in the MEASURES and DEFINE clauses of MATCH_RECOGNIZE.

For the generic first aggregate function, see first.

Syntax

first ( { colName | rowPatternVar.colName } [, offset ] )

colName: A column from the table_reference that MATCH_RECOGNIZE operates on.
rowPatternVar.colName: A column qualified by a row pattern variable name. Reads the column value from rows mapped to that variable in the current match. For example, FIRST(down.tstamp) returns the timestamp of the first row classified as down.
offset: An optional integral literal greater than or equal to 0. The offset relative to the first row. The default is 0.

A value of the type of colName. If the identified row is outside the current partition, returns NULL.