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

# MODE

> Returns the most frequent non-NULL value.

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

NULLs are ignored; all-NULL input yields NULL.

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

### Aggregate

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

### Window

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
mode(value [, deterministic]) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])
```

* Use a frame that includes all rows in the group or starts at the first row (`UNBOUNDED PRECEDING`). Sliding frames that drop earlier rows are not supported. RANGE frames with a numeric or time offset are not supported.

## Arguments

### Aggregate and 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 | Values whose frequencies are counted. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point). |
| deterministic | BOOLEAN constant expression | No | Choose the lowest value when frequencies tie. Must be a constant argument. Default: FALSE. |

## Returns

* Type: same SQL type as value
* NULL behavior: NULLs are ignored; all-NULL input yields NULL.
* Empty input: Returns NULL for an empty global aggregate or frame.

## Properties

| Property | Value |
| - | - |
| Deterministic | Partial (Default tie resolution is arbitrary. TRUE picks the lowest tied value when values are comparable.) |
| Supports DISTINCT | No |
| Supports window usage | Partial (Use all rows in the group, or a frame starting at its first row.) |
| Supports window frames | Partial (The frame must start at the first row in the group. See Syntax.) |
| Exact result | Yes |

## Usage notes

* The optional BOOLEAN argument must be a non-NULL constant. NULL and row-dependent values are rejected.
* Exact frequency state grows with the number of distinct 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.
* Supports DISTINCT: Adding DISTINCT does not remove duplicate input values.

## Examples

### Most frequent value

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t("value") AS (VALUES (1), (2), (2), (3), (2)) SELECT mode("value") AS result FROM t;
```

| result |
| - |
| 2 |

### Choose the lowest tied value

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t("value") AS (VALUES (3), (3), (1), (1), (2)) SELECT mode("value", TRUE) AS result FROM t;
```

| result |
| - |
| 1 |

## Limitations

* Use a frame that includes all rows in the group or starts at the first row (`UNBOUNDED PRECEDING`). Sliding frames that drop earlier rows are not supported. RANGE frames with a numeric or time offset are not supported.
* DISTINCT does not remove duplicate input values in grouped calls. Removing duplicates can change the result, so remove duplicates from the input separately when needed.


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