Skip to main content

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​

SQL
> 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)
Last updated on