max

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › Smallest, largest and any value. An aggregate function that returns the largest value of an expression across the rows of a query. It skips NULLs, and takes numbers, BOOLEAN, DATE, TIME, TIMESTAMP and INTERVAL values.

Syntax

max(x)
max(x) FILTER (WHERE condition)

Returns: the type of x. A HUGEINT result reaches Python through ADBC as a Decimal.

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_aggregate_max. 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 two tables: five sensor readings, the last of which has no values, and four special floating-point values (NaN, inf, -inf and 1.0):

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_aggregate_max"'

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}.readings"
SPECIAL = f"{SCHEMA}.special"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} "
    "(id BIGINT, sensor VARCHAR, temperature DOUBLE, ok BOOLEAN, day DATE, taken TIMESTAMP)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 's1', 18.5, true, DATE '2026-10-01', TIMESTAMP '2026-10-01 08:00:00'), "
    "(2, 's1', 21.0, true, DATE '2026-10-01', TIMESTAMP '2026-10-01 14:30:00'), "
    "(3, 's2', -3.5, false, DATE '2026-10-02', TIMESTAMP '2026-10-02 06:15:00'), "
    "(4, 's2', 24.0, true, DATE '2026-10-03', TIMESTAMP '2026-10-03 12:00:00'), "
    "(5, 's3', NULL, NULL, NULL, NULL)")
run(f"CREATE TABLE IF NOT EXISTS {SPECIAL} (id BIGINT, x DOUBLE)")
for row in ["(1, 'NaN'::DOUBLE)", "(2, 'inf'::DOUBLE)", "(3, '-inf'::DOUBLE)", "(4, 1.0)"]:
    run(f"INSERT INTO {SPECIAL} VALUES {row}")

[]
[]
[(5,)]
[]
[(1,)]
[(1,)]
[(1,)]
[(1,)]

What it does

max(x) reads x on every row the query keeps, skips the NULLs, and returns the largest value. A query with max() and no other columns returns one row.

max() doesn't take VARCHAR, BLOB, lists or structs; see Limitations and errors for what to use instead.

NULLs and edge cases

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

run(f"SELECT max(temperature), max(day) FROM {TABLE} WHERE id IN (1, 5)")
run(f"SELECT max(temperature), max(day) FROM {TABLE} WHERE id = 5")
run(f"SELECT max(temperature) FROM {TABLE} WHERE id > 100")
run("SELECT max(NULL), max(42)")
run(f"SELECT max(x), max(x) FILTER (WHERE id IN (2, 4)), max(x) FILTER (WHERE id IN (3, 4)) FROM {SPECIAL}")
run(f"SELECT max(DISTINCT temperature), max(temperature) FROM {TABLE}")
run(f"SELECT max(sensor) FROM {TABLE}")
run(f"SELECT max(temperature, 2) FROM {TABLE}")

[(18.5, datetime.date(2026, 10, 1))]
[(None, None)]
[(None,)]
[(None, 42)]
[(nan, inf, 1.0)]
[(24.0, 24.0)]
InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.

Examples

The highest temperature, the last day and the latest reading time. Each max() skips the empty reading:

run(f"SELECT max(temperature), max(day), max(taken) FROM {TABLE}")

[(24.0, datetime.date(2026, 10, 3), datetime.datetime(2026, 10, 3, 12, 0))]

The highest temperature of one sensor. The WHERE condition picks the rows before max() sees them:

run(f"SELECT max(temperature) FROM {TABLE} WHERE sensor = 's2'")

[(24.0,)]

The highest temperature of each sensor, in one query. GROUP BY isn't available, so each sensor gets its own FILTER clause:

run(f"SELECT max(temperature) FILTER (WHERE sensor = 's1') AS s1, "
    f"max(temperature) FILTER (WHERE sensor = 's2') AS s2 FROM {TABLE}")

[(21.0, 24.0)]

The whole row with the highest temperature. max() returns only the value; to get the row it's on, sort in descending order and keep the first row:

run(f"SELECT id, sensor, temperature FROM {TABLE} ORDER BY temperature DESC NULLS LAST LIMIT 1")

[(4, 's2', 24.0)]

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). An aggregate can't go in WHERE, so what matters is the plan of the aggregate query:

• The scan reads the columns the query uses. A WHERE condition that VAST DataBase can evaluate, such as a comparison of a column with a constant, is applied in the scan, so the rows it rejects never reach the Query Engine. It doesn't appear as a step in the plan.
• A WHERE condition the scan can't evaluate, such as one on a function of a column, appears as a FILTER step above the scan.
• One UNGROUPED_AGGREGATE step in the Query Engine computes max(), and every other aggregate in the SELECT list, over the rows it receives, and returns one row.

The plan of a max() with a pushed-down WHERE condition has only the aggregate step and the scan:

plan(f"SELECT max(temperature) FROM {TABLE} WHERE id > 1")

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│      max(temperature)     │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│        temperature        │
│                           │
└───────────────────────────┘

Make it faster

• Restrict the rows in WHERE, not in FILTER, when every aggregate needs the same rows. A FILTER (WHERE ...) condition is evaluated in the aggregate step, after the scan has sent every row and the column the condition reads. The plan shows it inside the UNGROUPED_AGGREGATE step:

plan(f"SELECT max(temperature) FILTER (WHERE id > 1) FROM {TABLE}")

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│  max(temperature) FILTER  │
│ (WHERE (id > 1)) Filter:  │
│          (id > 1)         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│        temperature        │
│                           │
└───────────────────────────┘

• Compute several aggregates in one query. SELECT max(temperature), min(temperature), max(day) ... reads the table once: the plan has one scan and one aggregate step for all three.
• Keep functions of a column out of WHERE when a plain comparison does the job. A condition such as abs(temperature) > 5 adds a FILTER step in the Query Engine; temperature > 5 is applied in the scan.

Limitations and errors

• No text or binary values. On a VARCHAR or BLOB, max() fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. To find the length of the longest string, use max(length(s)) (length). To find the last string in sort order, read it with SELECT s FROM ... WHERE s IS NOT NULL ORDER BY s DESC LIMIT 1.
• No lists or structs. They fail with The operator 'HASH_GROUP_BY' type=9 is currently not supported. For the largest element of each list, use list_max.
• No GROUP BY. SELECT sensor, max(temperature) ... GROUP BY sensor fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported. Use one FILTER clause per group, as in Examples, or run the query in Trino or Spark.
• No top-N form. max(x, n), the form that would return the n largest values as a list, fails with the same HASH_GROUP_BY error. Use ORDER BY x DESC NULLS LAST LIMIT n.
• No arg_max or max_by. They fail with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. Use ORDER BY x DESC NULLS LAST LIMIT 1, as in Examples.
• As a window function, only OVER (ROWS UNBOUNDED PRECEDING) runs, as a running maximum over the rows in the order the scan returns them. OVER (), OVER (ORDER BY ...) and OVER (PARTITION BY ...) fail with The operator 'WINDOW' type=6 is currently not supported. See window functions.
• Not in WHERE. WHERE max(x) > 0 fails with Binder Error: WHERE clause cannot contain aggregates!

run(f"SELECT sensor, max(temperature) FROM {TABLE} GROUP BY sensor")
run(f"SELECT arg_max(sensor, temperature) FROM {TABLE}")
run(f"SELECT id, max(temperature) OVER (ORDER BY id) FROM {TABLE}")
run(f"SELECT id FROM {TABLE} WHERE max(temperature) > 0")

InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.
InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
InternalError: The operator 'WINDOW' type=6 is currently not supported.
InternalError: Binder Error: WHERE clause cannot contain aggregates!

Similar functions

run(f"SELECT max(temperature), min(temperature), any_value(temperature) FROM {TABLE}")
run(f"SELECT max(length(sensor)) FROM {TABLE}")
run("SELECT list_max([3, 1, 2])")

[(24.0, -3.5, 18.5)]
[(2,)]
[(3,)]

Clean up:

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

[]
[]
[]

See also

• Aggregate and analytic functions: all the aggregates.
• SELECT: aggregates in a query, and EXPLAIN: how to read a plan.
• Pushdowns: which WHERE conditions VAST DataBase evaluates.