---
title: "least"
slug: "dev-database-reference-sql-functions-conditional-and-cast-least"
updated: 2026-10-30T18:18:46Z
published: 2026-10-30T18:18:46Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-conditional-and-cast-least"
---

> ## 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.

# least

*Applies to VAST Cluster 5.5.*

*[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Conditional and conversion functions](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast) › Largest and smallest argument.* Returns the smallest of its arguments, compared within one row. NULL arguments are skipped. To find the smallest value across rows, use the aggregate [min](/develop/docs/dev-database-reference-sql-functions-aggregate-min).

## Syntax

```
least(a [, b, ...])
```

| Argument | Type | Description |
| --- | --- | --- |
| `a`, `b`, ... | any type that orders | One or more values, of one type or of types that combine. |

**Returns:** the combined type of the arguments.

## 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_conditional_and_cast_least`. 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 a table of four players with scores from three rounds and some values missing. The last row has no values:

```
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_conditional_and_cast_least"'

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}.scores"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} "
    "(id BIGINT, r1 INTEGER, r2 BIGINT, r3 DOUBLE, name VARCHAR, team VARCHAR, "
    "joined DATE, played TIMESTAMP)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 12, 18, 9.5, 'ada', 'Blue', DATE '2026-10-01', TIMESTAMP '2026-10-16 18:00:00'), "
    "(2, 20, NULL, 15.0, 'Grace', 'red', DATE '2026-10-17', TIMESTAMP '2026-10-16 19:30:00'), "
    "(3, -4, 2, NULL, 'alan', NULL, DATE '2026-10-05', NULL), "
    "(4, NULL, NULL, NULL, NULL, NULL, NULL, NULL)")
```

```
[]
[]
[(4,)]
```

## What it does

`least(a, b, c)` compares its arguments with each other and returns the smallest. It skips NULL arguments, so the result is NULL only if every argument is NULL.

The arguments are combined into one type first: an `INTEGER` and a `BIGINT` give a `BIGINT`, an integer and a `DOUBLE` give a `DOUBLE`, `1` and `2.5` give a `DECIMAL(11,1)`, and a `DATE` and a `TIMESTAMP` give a `TIMESTAMP`. A text literal with a number is converted to the number's type, so `least(1, &apos;2&apos;)` is the `INTEGER` `1`.

How values order depends on their type:

| Type | Order | Example | Result |
| --- | --- | --- | --- |
| `VARCHAR` | by character code, not as numbers or words: `&apos;B&apos;` is smaller than `&apos;a&apos;`, and `&apos;f&apos;` smaller than `&apos;&#xe9;&apos;` | `least(&apos;a&apos;, &apos;B&apos;)`, `least(&apos;10&apos;, &apos;9&apos;)` | `&apos;B&apos;`, `&apos;10&apos;` |
| `DOUBLE` | `NaN` is larger than every number, so `least` returns the number | `least(&apos;nan&apos;::DOUBLE, 1)`, `least(&apos;inf&apos;::DOUBLE, &apos;nan&apos;::DOUBLE)` | `1.0`, `inf` |
| `INTERVAL` | `25 HOUR` is smaller than `1 DAY`, and `30 DAY` smaller than `1 MONTH` | `least(INTERVAL 1 DAY, INTERVAL 25 HOUR)` | 25 hours |
| list | element by element; a list that starts another is smaller; a value is smaller than a NULL element | `least([1, 2], [1, 2, 0])` | `[1, 2]` |
| `BOOLEAN` | `false` before `true` | `least(true, false)` | `false` |

`DATE`, `TIMESTAMP`, `TIME`, `BLOB`, `UUID` and structs order as well.

## NULLs and edge cases

| Input | Result |
| --- | --- |
| `least(NULL, 1)`, `least(1, NULL)` | `1` |
| `least(1, NULL, 3)` | `1` |
| `least(NULL, NULL)` | `NULL` |
| `least(1)` | `1` |
| `least(&apos;&apos;, &apos;a&apos;)` | `&apos;&apos;` |
| `least(-0.0::DOUBLE, 0.0::DOUBLE)` | `-0.0`: the two are equal |
| `least([1, NULL], [1, 2])` | `[1, 2]` |
| `least(1, true)` | `1` |
| `least()` | Error: `No function matches the given name and argument types &apos;least()&apos;` |
| `least(1, [1])` | Error: `Cannot combine types of INTEGER_LITERAL and INTEGER[] - an explicit cast is required` |
| `least(DATE &apos;2026-10-16&apos;, 1)` | Error: `Cannot combine types of DATE and INTEGER_LITERAL - an explicit cast is required` |

The same cases and the ordering rules, as the cluster returns them. They use the helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-least#set-up-the-examples):

```
run("SELECT least(NULL, 1), least(1, NULL), least(1, NULL, 3), least(NULL, NULL), "
    "least(1), least('', 'a'), least(-0.0::DOUBLE, 0.0::DOUBLE), least(1, true)")
run("SELECT least('a', 'B'), least('a', 'A'), least('10', '9'), least('\u00e9', 'f'), "
    "least('nan'::DOUBLE, 1), least('inf'::DOUBLE, 'nan'::DOUBLE), least(true, false)")
run("SELECT least(INTERVAL 1 DAY, INTERVAL 25 HOUR), least(INTERVAL 1 MONTH, INTERVAL 30 DAY), "
    "least([1, 2], [1, 3]), least([1, 2], [1, 2, 0]), least([1, NULL], [1, 2]), "
    "least({'a': 1}, {'a': 2})")
run("SELECT least(1, 2.5), typeof(least(1, 2.5)), least(1, '2'), typeof(least(1, '2')), "
    "least(DATE '2026-10-16', TIMESTAMP '2026-10-16 12:00:00')")
run("SELECT least()")
run("SELECT least(1, [1])")
run("SELECT least(DATE '2026-10-16', 1)")
```

```
[(1, 1, 1, None, 1, '', -0.0, 1)]
[('B', 'A', '10', 'f', 1.0, inf, False)]
[(MonthDayNano(months=0, days=0, nanoseconds=90000000000000), MonthDayNano(months=0, days=30, nanoseconds=0), [1, 2], [1, 2], [1, 2], {'a': 1})]
[(Decimal('1.0'), 'DECIMAL(11,1)', 1, 'INTEGER', datetime.datetime(2026, 10, 16, 0, 0))]
InternalError: Binder Error: No function matches the given name and argument types 'least()'. You might need to add explicit type casts.
InternalError: Binder Error: Cannot combine types of INTEGER_LITERAL and INTEGER[] - an explicit cast is required
InternalError: Binder Error: Cannot combine types of DATE and INTEGER_LITERAL - an explicit cast is required
```

## Examples

The worst round of each player. Missing rounds are skipped; player 4 has none, so the result is NULL. `r1` is an `INTEGER` and `r2` a `BIGINT`, so `least(r1, r2)` is a `BIGINT`; with the `DOUBLE` `r3` it's a `DOUBLE`:

```
run(f"SELECT id, least(r1, r2), least(r1, r2, r3), typeof(least(r1, r2, r3)) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, 12, 9.5, 'DOUBLE'), (2, 20, 15.0, 'DOUBLE'), (3, -4, -4.0, 'DOUBLE'), (4, None, None, 'DOUBLE')]
```

A ceiling for each value, and the earlier of two dates. `least(r1, 15)` caps the scores at `15`. A `DATE` and a `TIMESTAMP` give a `TIMESTAMP`:

```
run(f"SELECT id, least(r1, 15), least(joined, played) FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, 12, datetime.datetime(2026, 10, 1, 0, 0)), (2, 15, datetime.datetime(2026, 10, 16, 19, 30)), (3, -4, datetime.datetime(2026, 10, 5, 0, 0)), (4, 15, None)]
```

Text compares by character code, so `&apos;Blue&apos;` is smaller than `&apos;ada&apos;`. Adding `COLLATE nocase` to the first argument doesn't change these results:

```
run(f"SELECT id, least(name, team), least(name COLLATE nocase, team) FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, 'Blue', 'Blue'), (2, 'Grace', 'Grace'), (3, 'alan', 'alan'), (4, None, None)]
```

`least()` compares values in one row and `min()` values across rows; `least()` of two `min()` results gives the smallest value in two columns:

```
run(f"SELECT min(r1), min(r2), least(min(r1), min(r2)) FROM {TABLE}")
```

```
[(-4, 2, -4)]
```

## 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`). What happens to `least()` depends on its arguments:

- **On constants only,** such as `least(1, 2)`, it's computed once while the query is planned, and a comparison of a column with the result can still be pushed down to VAST DataBase.
- **On a column in the `SELECT` list,** the scan reads the columns and the Query Engine computes `least()` for each row it returns.
- **On a column in `WHERE`,** the scan sends the columns for every row, and a `FILTER` step in the Query Engine computes `least()` and keeps the rows that match. No rows are skipped in storage.

The first plan has a `FILTER` step above the scan; the second, with `least()` on constants, has none:

```
plan(f"SELECT id FROM {TABLE} WHERE least(r1, 0) < 0")
plan(f"SELECT id FROM {TABLE} WHERE r1 < least(1, 2)")
```

```
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│     (least(r1, 0) < 0)    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             r1            │
│             id            │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Compare the raw column when you can.** `least(r1, 0) &lt; 0` keeps the same rows as `r1 &lt; 0`, because `0` is never less than 0, and `r1 &lt; 0` is a comparison of a column with a constant, which VAST DataBase can evaluate:

```
plan(f"SELECT id FROM {TABLE} WHERE r1 < 0")
run(f"SELECT id FROM {TABLE} WHERE r1 < 0 ORDER BY id LIMIT 10")
```

```
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
[(3,)]
```

- **Store the value if you filter on it often.** Compute the worst score when you write the rows and keep it in its own column, which a comparison with a constant can filter in VAST DataBase.

## Limitations and errors

- **The arguments must combine.** A text column with a number column fails with `Binder Error: Cannot combine types of INTEGER and VARCHAR - an explicit cast is required`; a `DATE` with a number, or a number with a list, fails the same way. Cast one of them.
- **At least one argument.** `least()` fails with `Binder Error: No function matches the given name and argument types &apos;least()&apos;`. The error lists the form it takes: `least(ANY, [ANY...]) -&gt; ANY`.
- **Large integers become `HUGEINT`.** `least(9223372036854775807, 9223372036854775808)`, and a `UBIGINT` with a negative number, give a `HUGEINT`, which ADBC returns as a `DECIMAL(38, 0)`.
- **`least_ignore_nulls` isn't available.** It fails with `Catalog Error: Scalar Function with name least_ignore_nulls does not exist!`. `least` already skips NULLs.

```
run(f"SELECT least(r1, name) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, least(CAST(r1 AS VARCHAR), name) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT typeof(least(9223372036854775807, 9223372036854775808)), typeof(least(1::UBIGINT, -1))")
run("SELECT least_ignore_nulls(1, NULL)")
```

```
InternalError: Binder Error: Cannot combine types of INTEGER and VARCHAR - an explicit cast is required
[(1, '12'), (2, '20'), (3, '-4'), (4, None)]
[('HUGEINT', 'HUGEINT')]
InternalError: Catalog Error: Scalar Function with name least_ignore_nulls does not exist!
```

## Similar functions

| Function | Use it for | Example | Result |
| --- | --- | --- | --- |
| [greatest](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-greatest) | the largest argument | `greatest(2, 1)` | `2` |
| [min](/develop/docs/dev-database-reference-sql-functions-aggregate-min) | the smallest value across rows | `min(r1)` | the smallest `r1` |
| [coalesce](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-coalesce) | the first argument that isn't NULL | `coalesce(NULL, 3, 1)` | `3` |
| [CASE](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-case) | the smaller of two values, written out | `CASE WHEN 2 &lt; 1 THEN 2 ELSE 1 END` | `1` |

```
run("SELECT least(2, 1), greatest(2, 1), coalesce(NULL, 3, 1), CASE WHEN 2 < 1 THEN 2 ELSE 1 END")
run(f"SELECT min(r1), least(min(r1), min(r2)) FROM {TABLE}")
```

```
[(1, 2, 3, 1)]
[(-4, -4)]
```

Clean up:

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

```
[]
[]
```

## See also

- [Conditional and conversion functions](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast): all the conditional and conversion functions.
- [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns).
- [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.