Numbering functions in GoogleSQL

GoogleSQL for Spanner supports numbering functions. Numbering functions are a subset of window functions. To create a window function call and learn about the syntax for window functions, see Window function calls.

Numbering functions assign values to each row based on their position within the specified window. The OVER clause syntax varies across numbering functions.

Function list

Name Summary
IS_FIRST Returns true if this row is in the first k rows (1-based) within the window.

IS_FIRST

IS_FIRST(k)
OVER over_clause

over_clause:
  ( [ window_specification ] )

window_specification:
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]

Description

Returns true if the current row is in the first k rows (1-based) in the window; otherwise, returns false. This function doesn't require the ORDER BY clause.

Details

  • The k value must be positive; otherwise, a runtime error is raised.
  • If any rows are tied or if ORDER BY is omitted, the result is non-deterministic. If the ORDER BY clause is unspecified or if all rows are tied, the result is equivalent to ANY-k.

To learn more about the OVER clause and how to use it, see Window function calls.

Return Type

BOOL