any_value

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › Smallest, largest and any value. An aggregate function that returns one non-NULL value of an expression from the rows of a query. Use it to show a column whose value is the same on every row, next to other aggregates, where the plain column isn't allowed.

Syntax

any_value(x)
any_value(x) FILTER (WHERE condition)

Returns: the type of x.

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_any_value. 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 two floating-point values (NaN 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_any_value"'

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, 1.0)"]:
    run(f"INSERT INTO {SPECIAL} VALUES {row}")

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

What it does

any_value(x) returns the value of x from one of the rows the query keeps, skipping the rows where x is NULL. It doesn't compare the values, and which row it takes isn't something to depend on: when the values differ, use min or max to get a defined result.

The usual reason to use it is a query that mixes a column with aggregates. Without GROUP BY, a plain column next to an aggregate fails with column "..." must appear in the GROUP BY clause or must be part of an aggregate function. When the WHERE condition makes the column the same on every row, any_value(column) returns that value and the query runs. See Examples.

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

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

Examples

The day next to the warmest reading of that day. The plain day column fails, because it isn't inside an aggregate:

run(f"SELECT day, max(temperature) FROM {TABLE} WHERE day = DATE '2026-10-01'")

InternalError: Binder Error: column "day" must appear in the GROUP BY clause or must be part of an aggregate function.

any_value(day) returns the day, which the WHERE condition makes the same on every row:

run(f"SELECT any_value(day), max(temperature), count(*) FROM {TABLE} WHERE day = DATE '2026-10-01'")

[(datetime.date(2026, 10, 1), 21.0, 2)]

One FILTER clause per value restricts each any_value() to its own rows:

run(f"SELECT any_value(day) FILTER (WHERE id = 3) AS day_3, "
    f"any_value(day) FILTER (WHERE id = 4) AS day_4 FROM {TABLE}")

[(datetime.date(2026, 10, 2), datetime.date(2026, 10, 3))]

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

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

plan(f"SELECT any_value(day), max(temperature) FROM {TABLE} WHERE id <= 2")

┌───────────────────────────┐
│    UNGROUPED_AGGREGATE    │
│    ────────────────────   │
│        Aggregates:        │
│       any_value(day)      │
│      max(temperature)     │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            day            │
│        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 any_value(day) FILTER (WHERE id > 1) FROM {TABLE}")

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

• Put any_value() in the same query as the other aggregates. All the aggregates of a query share one scan and one aggregate step.

Limitations and errors

• No text or binary values. On a VARCHAR or BLOB, any_value() fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. To show a text value next to aggregates, run a second query, such as SELECT s FROM ... WHERE ... LIMIT 1.
• No lists or structs. They fail with The operator 'HASH_GROUP_BY' type=9 is currently not supported.
• No ORDER BY inside the call. any_value(x ORDER BY y) fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. To take the value from a chosen row, use ORDER BY y LIMIT 1 on the query.
• No GROUP BY. SELECT sensor, any_value(day) ... GROUP BY sensor fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported. Use one FILTER clause per group, or run the query in Trino or Spark.
• As a window function, only OVER (ROWS UNBOUNDED PRECEDING) runs. OVER (), OVER (ORDER BY ...) and OVER (PARTITION BY ...) fail with The operator 'WINDOW' type=6 is currently not supported. See window functions.

run(f"SELECT sensor, any_value(day) FROM {TABLE} GROUP BY sensor")
run(f"SELECT id, any_value(day) OVER () FROM {TABLE}")

InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.
InternalError: The operator 'WINDOW' type=6 is currently not supported.

Similar functions

run(f"SELECT any_value(day), min(day), max(day) FROM {TABLE}")
run(f"SELECT arbitrary(day) FROM {TABLE}")
run(f"SELECT first(day) FROM {TABLE}")
run(f"SELECT day FROM {TABLE} ORDER BY id LIMIT 1")

[(datetime.date(2026, 10, 1), datetime.date(2026, 10, 1), datetime.date(2026, 10, 3))]
InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
[(datetime.date(2026, 10, 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.