Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

Comparison Functions

  • Capella Operational
  • reference
    +
    Comparison functions determine the greatest or least value from a set of values.

    GREATEST(expr1 , expr2, …​)

    Description

    Returns the greatest value from a list of expressions.

    The function compares values based on the sort order of their data types. For example, it compares strings alphabetically and dates chronologically. When comparing values of different data types, the function uses collation rules to determine precedence.

    The function requires at least 2 expressions and ignores NULL and MISSING when evaluating values.

    Arguments

    expr1, expr2, …​

    The expressions to compare. You must specify at least 2 expressions.

    Return Value

    The function returns:

    • The largest value among the provided expressions.

    • NULL if all expressions are NULL or MISSING.

    Examples

    Example 1. Find the greatest value from a list of numbers
    Query
    SELECT GREATEST(19.50, 15, 21.50, 18) AS greatest_number;
    Result
    [
  {
    "greatest_number": 21.5
  }
]
    Example 2. Find the greatest value from a list of strings
    Query
    SELECT GREATEST("United", "Delta", "American", "Southwest")
AS last_airline_name;
    Result
    [
  {
    "last_airline_name": "United"
  }
]

    When comparing string values, the function uses alphabetical order to determine the greatest value.

    Example 3. Find the greatest value from a list with mixed types
    Query
    SELECT GREATEST(42, "airline", "2025-12-01T00:00:00Z", NULL)
AS greatest_value;
    Result
    [
  {
    "greatest_value": "airline"
  }
]

    The function returns "airline" because strings have a higher precedence than numbers and dates.

    LEAST(expr1 , expr2, …​)

    Description

    Returns the smallest value from a list of expressions.

    The function compares values based on the sort order of their data types. For example, it compares strings alphabetically and dates chronologically. When comparing values of different data types, the function uses collation rules to determine precedence.

    The function requires at least 2 expressions and ignores NULL and MISSING when evaluating values.

    Arguments

    expr1, expr2, …​

    The expressions to compare. You must specify at least 2 expressions.

    Return Value

    The function returns:

    • The smallest value among the provided expressions.

    • NULL if all expressions are NULL or MISSING.

    Examples

    Example 4. Find the least value from a list of numbers
    Query
    SELECT LEAST(19.50, 15, 21.50, 18) AS lowest_number;
    Result
    [
  {
    "lowest_number": 15
  }
]
    Example 5. Find the least value from a list of strings
    Query
    SELECT LEAST("United", "Delta", "American", "Southwest")
AS first_airline_name;
    Result
    [
  {
    "first_airline_name": "American"
  }
]

    When comparing string values, the function uses alphabetical order to determine the least value.

    Example 6. Find the least value from a list with mixed types
    Query
    SELECT LEAST(42, "airline", "2025-12-01T00:00:00Z", NULL)
AS least_value;
    Result
    [
  {
    "least_value": 42
  }
]

    The function returns 42 because numbers have a lower precedence than strings and dates.