MIN

Description

The MIN function returns the minimum 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.

Syntax

MIN(<expr>)

Parameters

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.

Return Value

Returns the same data type as the input expression. If all records in the group are NULL, the function returns NULL.

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.

Example

-- 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, min(k_string) from t1 group by k1 order by k1;

String type: For each group, returns the minimum string value.

+------+---------------+
| k1   | min(k_string) |
+------+---------------+
|    1 | apple         |
|    2 | orange        |
|    3 | NULL          |
+------+---------------+

select k1, min(k_decimal) from t1 group by k1 order by k1;

Decimal type: Returns the minimum high-precision decimal value.

+------+----------------+
| k1   | min(k_decimal) |
+------+----------------+
|    1 |          10.01 |
|    2 |          30.03 |
|    3 |           NULL |
+------+----------------+

select k1, min(k_array) from t1 group by k1 order by k1;

For Array type: Compares elements in order and returns the minimum array value for each group. NULL elements compare greater than non-NULL elements at the same position.

+------+----------------+
| k1   | min(k_array)   |
+------+----------------+
|    1 | [10, 20]       |
|    2 | [10, 20, 40]   |
|    3 | NULL           |
+------+----------------+

select min(k_string) from t1 where k1 = 3;

When all values in the group are NULL, returns NULL.

+---------------+
| min(k_string) |
+---------------+
| NULL          |
+---------------+

select min(k_string) from t1;

Minimum value of all data.

+---------------+
| min(k_string) |
+---------------+
| apple         |
+---------------+