min

Applies to VAST Cluster 5.5.

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

Syntax

min(x)
min(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_min. 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_min"'

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

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

min() 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 min(temperature), min(day) FROM {TABLE} WHERE id IN (1, 5)")
run(f"SELECT min(temperature), min(day) FROM {TABLE} WHERE id = 5")
run(f"SELECT min(temperature) FROM {TABLE} WHERE id > 100")
run("SELECT min(NULL), min(42)")
run(f"SELECT min(x), min(x) FILTER (WHERE id IN (2, 4)) FROM {SPECIAL}")
run(f"SELECT min(DISTINCT temperature), min(temperature) FROM {TABLE}")
run(f"SELECT min(sensor) FROM {TABLE}")
run(f"SELECT min(temperature, 2) FROM {TABLE}")

[(18.5, datetime.date(2026, 10, 1))]
[(None, None)]
[(None,)]
[(None, 42)]
[(-inf, 1.0)]
[(-3.5, -3.5)]
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 lowest temperature, the first day and the earliest reading time. Each min() skips the empty reading:

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

[(-3.5, datetime.date(2026, 10, 1), datetime.datetime(2026, 10, 1, 8, 0))]

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

run(f"SELECT min(temperature) FROM {TABLE} WHERE sensor = 's1'")

[(18.5,)]

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

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

[(18.5, -3.5)]

The whole row with the lowest temperature. min() returns only the value; to get the row it's on, sort and keep the first row:

run(f"SELECT id, sensor, temperature FROM {TABLE} WHERE temperature IS NOT NULL ORDER BY temperature LIMIT 1")

[(3, 's2', -3.5)]

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 min(), and every other aggregate in the SELECT list, over the rows it receives, and returns one row.

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

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

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│      min(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 min(temperature) FILTER (WHERE id > 1) FROM {TABLE}")

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

• Compute several aggregates in one query. SELECT min(temperature), max(temperature), min(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, min() fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. To find the shortest string, use min(length(s)) (length). To find the first string in sort order, read it with SELECT s FROM ... WHERE s IS NOT NULL ORDER BY s LIMIT 1.
• No lists or structs. They fail with The operator 'HASH_GROUP_BY' type=9 is currently not supported. For the smallest element of each list, use list_min.
• No GROUP BY. SELECT sensor, min(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. min(x, n), the form that would return the n smallest values as a list, fails with the same HASH_GROUP_BY error. Use ORDER BY x LIMIT n.
• No arg_min or min_by. They fail with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. Use ORDER BY x LIMIT 1, as in Examples.
• As a window function, only OVER (ROWS UNBOUNDED PRECEDING) runs, as a running minimum 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 min(x) > 0 fails with Binder Error: WHERE clause cannot contain aggregates!

run(f"SELECT sensor, min(temperature) FROM {TABLE} GROUP BY sensor")
run(f"SELECT arg_min(sensor, temperature) FROM {TABLE}")
run(f"SELECT id, min(temperature) OVER (ORDER BY id) FROM {TABLE}")
run(f"SELECT id FROM {TABLE} WHERE min(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 min(temperature), max(temperature), any_value(temperature) FROM {TABLE}")
run(f"SELECT min(length(sensor)) FROM {TABLE}")
run("SELECT list_min([3, 1, 2])")

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

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.