mode aggregate function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.2 and above

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

Syntax 

mode(expr) [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.

  • 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