> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# ARBITRARY

> Returns an arbitrary value from a group.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">Aggregate · Window</span></div>

**Aliases:** [`any_value`](/query/sql/functions/aggregate/any_value)

A selected value can be NULL; it is not guaranteed to select a non-NULL row.

## Syntax

A window function keeps each input row and adds a result for that row. `PARTITION BY` groups rows with matching values; without it, all input rows form one group. A frame from `UNBOUNDED PRECEDING` to `UNBOUNDED FOLLOWING` includes every row in the group.

### Aggregate

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
arbitrary([DISTINCT] value)
```

### Window over all rows in each group

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
arbitrary(value) OVER ([PARTITION BY key] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
```

* Use the full-group window shown above. Other frame boundaries, ordering options, and NULL-handling clauses are outside the forms covered on this page.

## Arguments

### Aggregate and full-group window

| Name | Type | Required | Description |
| - | - | - | - |
| value | BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY, MAP or STRUCT | Yes | Value to choose. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point). |

## Returns

* Type: same SQL type as value
* NULL behavior: A selected value can be NULL; it is not guaranteed to select a non-NULL row.
* Empty input: Returns NULL on empty input.

## Properties

| Property | Value |
| - | - |
| Deterministic | No |
| Supports DISTINCT | Partial (Accepted, but has no effect.) |
| Supports window usage | Partial (Use the full-group window shown in Syntax.) |
| Supports window frames | Partial (This page covers a frame from the first through the last row in each group.) |
| Exact result | Yes |

## Usage notes

* A NULL value can be selected, even when the group also contains non-NULL values.
* INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
* Query tools may report a DECIMAL result as DOUBLE or a TIME result as INTEGER. The returned value still keeps its decimal precision and scale, or its time type. A reported type does not convert the value.
* Deterministic: The selected value can change if the input order changes.
* DISTINCT has no effect and does not make the function skip NULL values.

## Examples

### Choose a value within each group

One possible result is shown. Group a can return 7 or 9; group b can return 10 or 20. Do not rely on a particular choice.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t(g, v) AS (VALUES ('a', 7), ('a', 9), ('b', 10), ('b', 20)) SELECT g, arbitrary(v) AS result FROM t GROUP BY g ORDER BY g NULLS LAST;
```

| g | result |
| - | - |
| a | 7 |
| b | 10 |

### Keep every row with a full-group window

The frame includes every row in each group. The query keeps all four input rows and adds a chosen value from that row’s group. This is one possible result; the chosen values can change.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t(id, g, v) AS (VALUES (1, 'a', 7), (2, 'a', 9), (3, 'b', 10), (4, 'b', 20)) SELECT id, g, v, arbitrary(v) OVER (PARTITION BY g ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS chosen FROM t ORDER BY id NULLS LAST;
```

| id | g | v | chosen |
| - | - | - | - |
| 1 | a | 7 | 7 |
| 2 | a | 9 | 7 |
| 3 | b | 10 | 10 |
| 4 | b | 20 | 10 |

### A group containing only NULL

A group containing only NULL returns NULL. A mixed group can also return NULL; this function does not promise to choose a non-NULL value.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t(v) AS (VALUES (CAST(NULL AS INTEGER))) SELECT arbitrary(v) AS result FROM t;
```

| result |
| - |
| NULL |

## Limitations

* The result can be NULL even when other values are non-NULL. Adding DISTINCT does not make the function skip NULL values.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.