--- title: "max" slug: "dev-database-reference-sql-functions-aggregate-max" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-aggregate-max" --- > ## 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. # max *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 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) ``` | 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 take the largest 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`. 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)](/develop/docs/dev-database-integrations-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](/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 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, "") 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. | Type of `x` | Largest means | Result type | | --- | --- | --- | | Integers, `FLOAT`, `DOUBLE`, `DECIMAL` | numerically largest; for `FLOAT` and `DOUBLE`, `NaN` counts as larger than `inf` | the type of `x`; the type's limits, such as `9223372036854775807` for `BIGINT`, come back exactly | | `BOOLEAN` | `true` after `false` | `BOOLEAN` | | `DATE`, `TIMESTAMP` | latest | the type of `x` | | `TIME` | latest time of day | `TIME` | | `INTERVAL` | longest | `INTERVAL` | `max()` doesn't take `VARCHAR`, `BLOB`, lists or structs; see [Limitations and errors](/develop/docs/dev-database-reference-sql-functions-aggregate-max#limitations-and-errors) for what to use instead. ## NULLs and edge cases | Input | Result | | --- | --- | | Some values NULL | NULLs are skipped | | Only NULLs | `NULL` | | No rows | `NULL` | | `max(NULL)` | `NULL` | | `max(42)`, no `FROM` | `42` | | `NaN`, `inf`, `-inf` and `1.0` | `nan` | | `inf` and `1.0` | `inf` | | `-inf` and `1.0` | `1.0` | | `max(DISTINCT x)` | the same as `max(x)` | | A `VARCHAR` | Error: `The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.` | | A second argument, `max(x, n)` | Error: `The operator 'HASH_GROUP_BY' type=9 is currently not supported.` | 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-max#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](/develop/docs/dev-database-reference-sql-functions-string-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](/develop/docs/dev-database-reference-sql-functions-lists-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](/develop/docs/dev-database-reference-sql-functions-aggregate-max#examples), or run the query in [Trino](/develop/docs/dev-database-integrations-trino) or [Spark](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-functions-aggregate-max#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](/develop/docs/dev-database-reference-sql-functions-aggregate-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 | Function | Returns | On the readings' temperatures | | --- | --- | --- | | [min](/develop/docs/dev-database-reference-sql-functions-aggregate-min) | the smallest value | the lowest temperature | | [any_value](/develop/docs/dev-database-reference-sql-functions-aggregate-any-value) | a non-NULL value, without choosing | one of the temperatures | | `max(length(s))` | the length of the longest string | (text columns) | | [list_max](/develop/docs/dev-database-reference-sql-functions-lists-list-max) | the largest element of a list, per row | (list columns) | | `ORDER BY x DESC NULLS LAST LIMIT 1` | the row with the largest value | the row of the highest temperature | ``` 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](/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.