MODE

​

Syntax

​

Aggregate

mode(value [, deterministic])

​

Window

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

​

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

​

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

WITH t("value") AS (VALUES (1), (2), (2), (3), (2)) SELECT mode("value") AS result FROM t;

​

Choose the lowest tied value

WITH t("value") AS (VALUES (3), (3), (1), (1), (2)) SELECT mode("value", TRUE) AS result FROM t;

​

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.