The MAX function returns the maximum non-NULL value of the expression.
Version difference: In the Doris 4.1 series since 4.1.5, and in Doris 4.2 and later, a NULL element inside an array compares greater than a non-NULL element. In Doris 4.0 and Doris 4.1 before 4.1.5, a NULL array element compares smaller. A NULL array value is ignored in all these versions.
MAX(<expr>)
| Parameter | Description |
|---|---|
<expr> | The expression to get the value. Supported types are String, Time, Date, DateTime, Timestamptz, IPv4, IPv6, TinyInt, SmallInt, Integer, BigInt, LargeInt, Float, Double, Decimal, Array. |
Returns the same data type as the input expression. If all records in the group are NULL, the function returns NULL.
:::caution Behavior change (4.1.5)
In the Doris 4.1 series since 4.1.5, NULL elements inside complex values such as arrays compare greater than non-NULL elements at the same position. For example, [10, 20, NULL] is greater than [10, 20, 40]. This differs from an entire expression being NULL: those rows are still ignored. Before 4.1.5, aggregate comparisons treated NULL elements inside complex values as smaller.
:::
-- setup create table t1( k1 int, k_string varchar(100), k_decimal decimal(10, 2), k_array array<int> ) distributed by hash (k1) buckets 1 properties ("replication_num"="1"); insert into t1 values (1, 'apple', 10.01, [10, 20, 30]), (1, 'banana', 20.02, [10, 20]), (2, 'orange', 30.03, [10, 20, 40]), (2, null, null, [10, 20, null]), (3, null, null, null);
Table created and data inserted successfully.
select k1, max(k_string) from t1 group by k1 order by k1;
For String type: Returns the maximum string value for each group.
+------+---------------+ | k1 | max(k_string) | +------+---------------+ | 1 | banana | | 2 | orange | | 3 | NULL | +------+---------------+
select k1, max(k_decimal) from t1 group by k1 order by k1;
For Decimal type: Returns the maximum high-precision decimal value.
+------+----------------+ | k1 | max(k_decimal) | +------+----------------+ | 1 | 20.02 | | 2 | 30.03 | | 3 | NULL | +------+----------------+
select k1, max(k_array) from t1 group by k1 order by k1;
For Array type: Compares elements in order and returns the maximum array value for each group. NULL elements compare greater than non-NULL elements at the same position.
+------+----------------+ | k1 | max(k_array) | +------+----------------+ | 1 | [10, 20, 30] | | 2 | [10, 20, null] | | 3 | NULL | +------+----------------+
select max(k_string) from t1 where k1 = 3;
When all values in the group are NULL, returns NULL.
+---------------+ | max(k_string) | +---------------+ | NULL | +---------------+
select max(k_string) from t1;
Returns the maximum value across all data.
+---------------+ | max(k_string) | +---------------+ | orange | +---------------+