greatest

Applies to VAST Cluster 5.5.

Functions › Conditional and conversion functions › Largest and smallest argument. Returns the largest of its arguments, compared within one row. NULL arguments are skipped. To find the largest value across rows, use the aggregate max.

Syntax

greatest(a [, b, ...])

Returns: the combined type of the arguments.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_conditional_and_cast_greatest. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of four players with scores from three rounds and some values missing. The last row has no values:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_conditional_and_cast_greatest"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.scores"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} "
    "(id BIGINT, r1 INTEGER, r2 BIGINT, r3 DOUBLE, handicap DECIMAL(5, 2), name VARCHAR, team VARCHAR, "
    "joined DATE, played TIMESTAMP)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 12, 18, 9.5, 1.50, 'ada', 'Blue', DATE '2026-10-01', TIMESTAMP '2026-10-16 18:00:00'), "
    "(2, 20, NULL, 15.0, 0.25, 'Grace', 'red', DATE '2026-10-17', TIMESTAMP '2026-10-16 19:30:00'), "
    "(3, -4, 2, NULL, NULL, 'alan', NULL, DATE '2026-10-05', NULL), "
    "(4, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

greatest(a, b, c) compares its arguments with each other and returns the largest. It skips NULL arguments, so the result is NULL only if every argument is NULL.

The arguments are combined into one type first: an INTEGER and a BIGINT give a BIGINT, an integer and a DOUBLE give a DOUBLE, 1 and 2.5 give a DECIMAL(11,1), and a DATE and a TIMESTAMP give a TIMESTAMP. A text literal with a number is converted to the number's type, so greatest(1, '2') is the INTEGER 2.

How values order depends on their type:

DATE, TIMESTAMP, TIME, BLOB, UUID and structs order as well.

NULLs and edge cases

The same cases and the ordering rules, as the cluster returns them. They use the helpers from Set up the examples:

run("SELECT greatest(NULL, 1), greatest(1, NULL), greatest(1, NULL, 3), greatest(NULL, NULL), "
    "greatest(1), greatest('', 'a'), greatest(-0.0::DOUBLE, 0.0::DOUBLE), greatest(1, true)")
run("SELECT greatest('a', 'B'), greatest('a', 'A'), greatest('10', '9'), greatest('\u00e9', 'f'), "
    "greatest('nan'::DOUBLE, 1), greatest('inf'::DOUBLE, 'nan'::DOUBLE), greatest(true, false)")
run("SELECT greatest(INTERVAL 1 DAY, INTERVAL 25 HOUR), greatest(INTERVAL 1 MONTH, INTERVAL 30 DAY), "
    "greatest([1, 2], [1, 3]), greatest([1, 2], [1, 2, 0]), greatest([1, NULL], [1, 2]), "
    "greatest({'a': 1}, {'a': 2})")
run("SELECT greatest(1, 2.5), typeof(greatest(1, 2.5)), greatest(1, '2'), typeof(greatest(1, '2')), "
    "greatest(DATE '2026-10-16', TIMESTAMP '2026-10-16 12:00:00')")
run("SELECT greatest()")
run("SELECT greatest(1, [1])")
run("SELECT greatest(DATE '2026-10-16', 1)")

[(1, 1, 3, None, 1, 'a', -0.0, 1)]
[('a', 'a', '9', 'é', nan, nan, True)]
[(MonthDayNano(months=0, days=1, nanoseconds=0), MonthDayNano(months=1, days=0, nanoseconds=0), [1, 3], [1, 2, 0], [1, None], {'a': 2})]
[(Decimal('2.5'), 'DECIMAL(11,1)', 2, 'INTEGER', datetime.datetime(2026, 10, 16, 12, 0))]
InternalError: Binder Error: No function matches the given name and argument types 'greatest()'. You might need to add explicit type casts.
InternalError: Binder Error: Cannot combine types of INTEGER_LITERAL and INTEGER[] - an explicit cast is required
InternalError: Binder Error: Cannot combine types of DATE and INTEGER_LITERAL - an explicit cast is required

Examples

The best round of each player. Missing rounds are skipped; player 4 has none, so the result is NULL. r1 is an INTEGER and r2 a BIGINT, so greatest(r1, r2) is a BIGINT; with the DOUBLE r3 it's a DOUBLE:

run(f"SELECT id, greatest(r1, r2), greatest(r1, r2, r3), typeof(greatest(r1, r2, r3)) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 18, 18.0, 'DOUBLE'), (2, 20, 20.0, 'DOUBLE'), (3, 2, 2.0, 'DOUBLE'), (4, None, None, 'DOUBLE')]

A floor for each value, and the later of two dates. greatest(r1, 0) replaces the negative score with 0. A DATE and a TIMESTAMP give a TIMESTAMP:

run(f"SELECT id, greatest(r1, 0), greatest(joined, played) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 12, datetime.datetime(2026, 10, 16, 18, 0)), (2, 20, datetime.datetime(2026, 10, 17, 0, 0)), (3, 0, datetime.datetime(2026, 10, 5, 0, 0)), (4, 0, None)]

Text compares by character code, so 'ada' is larger than 'Blue'. Adding COLLATE nocase to the first argument doesn't change these results:

run(f"SELECT id, greatest(name, team), greatest(name COLLATE nocase, team) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'ada', 'ada'), (2, 'red', 'red'), (3, 'alan', 'alan'), (4, None, None)]

greatest() compares values in one row and max() values across rows; greatest() of two max() results gives the largest value in two columns:

run(f"SELECT max(r1), max(r2), greatest(max(r1), max(r2)) FROM {TABLE}")

[(20, 18, 20)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). What happens to greatest() depends on its arguments:

• On constants only, such as greatest(1, 2), it's computed once while the query is planned, and a comparison of a column with the result can still be pushed down to VAST DataBase.
• On a column in the SELECT list, the scan reads the columns and the Query Engine computes greatest() for each row it returns.
• On a column in WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine computes greatest() and keeps the rows that match. No rows are skipped in storage.

The first plan has a FILTER step above the scan; the second, with greatest() on constants, has none:

plan(f"SELECT id FROM {TABLE} WHERE greatest(r1, r2) > 15")
plan(f"SELECT id FROM {TABLE} WHERE r1 > greatest(1, 2)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│    (greatest(CAST(r1 AS   │
│     BIGINT), r2) > 15)    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             r1            │
│             r2            │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Make it faster

• Compare the raw column when you can. greatest(r1, 0) > 15 keeps the same rows as r1 > 15, because 0 is never greater than 15, and r1 > 15 is a comparison of a column with a constant, which VAST DataBase can evaluate:

plan(f"SELECT id FROM {TABLE} WHERE r1 > 15")
run(f"SELECT id FROM {TABLE} WHERE r1 > 15 ORDER BY id LIMIT 10")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
[(2,)]

• Store the value if you filter on it often. Compute the best score when you write the rows and keep it in its own column, which a comparison with a constant can filter in VAST DataBase.

Limitations and errors

• The arguments must combine. A text column with a number column fails with Binder Error: Cannot combine types of INTEGER and VARCHAR - an explicit cast is required; a DATE with a number, or a number with a list, fails the same way. Cast one of them.
• At least one argument. greatest() fails with Binder Error: No function matches the given name and argument types 'greatest()'. The error lists the form it takes: greatest(ANY, [ANY...]) -> ANY.
• Large integers become HUGEINT. greatest(9223372036854775807, 9223372036854775808), and a UBIGINT with a negative number, give a HUGEINT, which ADBC returns as a DECIMAL(38, 0).
• greatest_ignore_nulls isn't available. It fails with Catalog Error: Scalar Function with name greatest_ignore_nulls does not exist!. greatest already skips NULLs.

run(f"SELECT greatest(r1, name) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, greatest(CAST(r1 AS VARCHAR), name) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT typeof(greatest(9223372036854775807, 9223372036854775808)), typeof(greatest(1::UBIGINT, -1))")
run("SELECT greatest_ignore_nulls(1, NULL)")

InternalError: Binder Error: Cannot combine types of INTEGER and VARCHAR - an explicit cast is required
[(1, 'ada'), (2, 'Grace'), (3, 'alan'), (4, None)]
[('HUGEINT', 'HUGEINT')]
InternalError: Catalog Error: Scalar Function with name greatest_ignore_nulls does not exist!

Similar functions

run("SELECT greatest(2, 1), least(2, 1), coalesce(NULL, 1, 3), CASE WHEN 2 > 1 THEN 2 ELSE 1 END")
run(f"SELECT max(r1), greatest(max(r1), max(r2)) FROM {TABLE}")

[(2, 1, 1, 2)]
[(20, 20)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

• Conditional and conversion functions: all the conditional and conversion functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.