Comparison Functions

  • Capella Columnar
  • reference
December 15, 2024
+ 12
This topic describes the builtin SQL++ for Capella columnar comparison functions.

Comparison Functions

greatest

  • Syntax:

    greatest(numeric_value1, numeric_value2, ...)
  • Computes the greatest value among arguments.

  • Arguments:

    • numeric_value1: a tinyint/smallint/integer/bigint/float/double value,

    • numeric_value2: a tinyint/smallint/integer/bigint/float/double value,

    • …​.

  • Return Value:

    • the greatest values among arguments. The returning type is decided by the item type with the highest order in the numeric type promotion order (tinyint-> smallint->integer->bigint->float->double) among items.

    • null if any argument is a missing value or null value,

    • any other non-numeric input value causes a type error.

  • Example:

    { "v1": greatest(1, 2, 3), "v2": greatest(float("0.5"), double("-0.5"), 5000) };
  • The expected result is:

    { "v1": 3, "v2": 5000.0 }

least

  • Syntax:

    least(numeric_value1, numeric_value2, ...)
  • Computes the least value among arguments.

  • Arguments:

    • numeric_value1: a tinyint/smallint/integer/bigint/float/double value,

    • numeric_value2: a tinyint/smallint/integer/bigint/float/double value,

    • …​.

  • Return Value:

    • the least values among arguments. The returning type is decided by the item type with the highest order in the numeric type promotion order (tinyint-> smallint->integer->bigint->float->double) among items.

    • null if any argument is a missing value or null value,

    • any other non-numeric input value causes a type error.

  • Example:

    { "v1": least(1, 2, 3), "v2": least(float("0.5"), double("-0.5"), 5000) };
  • The expected result is:

    { "v1": 1, "v2": -0.5 }