mode aggregate function (Databricks SQL)

Returns the most frequent, not NULL, value of expr in a group.

Requires: SQL warehouse version 2022.35 or higher. This version is available in the Preview channel.

Syntax

mode(expr) [FILTER ( WHERE cond ) ]

Arguments

  • expr: An expression of any type that can be compared.

  • cond: An optional boolean expression filtering the rows used for aggregation.

Returns

The result type matches the type of the argument.

If the group contains only nulls, the function returns NULL. The result is non-deterministic if there is a tie for the most frequent value.

Examples

> SELECT mode(col) FROM VALUES (NULL), (1), (NULL), (2), (NULL), (3), (3) AS tab(col);
 3

> SELECT mode(col) FROM VALUES (array(1, 2)), (array(1, 2)), (array(2, 3)) AS tab(col);
 [1, 2]

-- The function returns either 1 or 2, but not 3
> SELECT mode(col) FROM VALUES (1), (1), (2), (2), (3) AS tab(col);
 1

> SELECT mode(col) FROM VALUES (NULL), (NULL) AS tab(col);
 NULL