--- title: "any_value" slug: "dev-database-reference-sql-functions-aggregate-any-value" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-aggregate-any-value" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # any_value *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Aggregate and analytic functions](/develop/docs/dev-database-reference-sql-functions-aggregate) › 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) ``` | Argument | Type | Description | | --- | --- | --- | | `x` | A number (`TINYINT`, `SMALLINT`, `INTEGER`, `BIGINT`, `HUGEINT`, `UTINYINT`, `USMALLINT`, `UINTEGER`, `UBIGINT`, `FLOAT`, `DOUBLE`, `DECIMAL`), `BOOLEAN`, `DATE`, `TIME`, `TIMESTAMP` or `INTERVAL` | The values to return one of: a column or an expression. | | `condition` | `BOOLEAN` | Optional. Only the rows where it's true count. See [FILTER](/develop/docs/dev-database-reference-sql-functions-aggregate-filter-clause). | **Returns:** the type of `x`. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), 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, "") 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](/develop/docs/dev-database-reference-sql-functions-aggregate-min) or [max](/develop/docs/dev-database-reference-sql-functions-aggregate-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](/develop/docs/dev-database-reference-sql-functions-aggregate-any-value#examples). ## NULLs and edge cases | Input | Result | | --- | --- | | Some values NULL | a non-NULL value | | Only NULLs | `NULL` | | No rows | `NULL` | | `any_value(NULL)` | `NULL` | | `any_value(42)`, no `FROM` | `42` | | Every value the same | that value, as `min()` and `max()` return | | `NaN` | `nan`: it's a value, not a NULL, so it isn't skipped | | `any_value(DISTINCT x)` | runs, like `any_value(x)` | | `ORDER BY` inside the call | Error: `The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.` | | A `VARCHAR` | Error: `The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.` | The same cases, as the cluster returns them. They use the tables and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-aggregate-any-value#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](/develop/docs/dev-database-integrations-trino) or [Spark](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-functions-aggregate-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 | Function | Returns | Instead | | --- | --- | --- | | [min](/develop/docs/dev-database-reference-sql-functions-aggregate-min), [max](/develop/docs/dev-database-reference-sql-functions-aggregate-max) | the smallest or largest value: a defined result when the values differ | | | [list_any_value](/develop/docs/dev-database-reference-sql-functions-lists-list-any-value) | the same idea for the elements of a list, per row | | | `arbitrary` | not available: `The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.` | `any_value(x)` | | `first`, `last` | not available: the same error | `SELECT x FROM ... LIMIT 1`, or `ORDER BY ... LIMIT 1` for a chosen row | ``` 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](/develop/docs/dev-database-reference-sql-functions-aggregate): all the aggregates. - [SELECT](/develop/docs/dev-database-reference-sql-select#aggregates): aggregates in a query, and [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan. - [Pushdowns](/develop/docs/dev-database-reference-pushdowns): which `WHERE` conditions VAST DataBase evaluates.