mode
aggregate function
Applies to: Databricks SQL
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. Iftrue
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.
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
Related functions
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for