mode aggregate function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above

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

mode is a non-deterministic function unless deterministic is set to true.

Syntax 

mode(expr [, deterministic ]) [FILTER ( WHERE cond ) ]

This function can also be invoked as a window function using the OVER clause.

Arguments

  • expr

    An expression of any type that can be compared.

  • deterministic

    In Databricks SQL and Databricks Runtime 14.1 and above. An optional BOOLEAN constant expression. If true guarantees a deterministic result if there are multiple values with the same frequency.

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

Note

Even with deterministic set to true, results might be non-deterministic for certain STRING collations, such as UTF8_LCASE.

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

> SELECT mode(col COLLATE UTF8_LCASE, true) FROM VALUES('a'), ('A'), ('b') AS tab(col);
 a (or A)