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

  • 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.