argMin

Alias: min_by

Introduced in: v1.1.0 Calculates the arg value for a minimum val value. If there are multiple rows with equal val being the maximum, which of the associated arg is returned is not deterministic. Both parts the arg and the min behave as aggregate functions, they both skip Null during processing and return not Null values if not Null values are available. See also

Tuple

Syntax

argMin(arg, val)

Aliases: min_by Arguments

arg — Argument for which to find the maximum value. const String
val — The minimum value. (U)Int8/16/32/64 or Float* or Date or DateTime or Tuple

Returned value Returns the arg value that corresponds to minimum val value. Type matches arg type. Examples Basic usage

Query

SELECT argMin(user, salary) FROM salary;

Response

┌─argMin(user, salary)─┐
│ worker               │
└──────────────────────┘

Extended example with NULL handling

Query

CREATE TABLE test
(
    a Nullable(String),
    b Nullable(Int64)
)
ENGINE = Memory AS
SELECT *
FROM VALUES((NULL, 0), ('a', 1), ('b', 2), ('c', 2), (NULL, NULL), ('d', NULL));

SELECT argMin(a, b), min(b) FROM test;

Response

┌─argMin(a, b)─┬─min(b)─┐
│ a            │      0 │
└──────────────┴────────┘

Using Tuple in arguments

Query

SELECT argMin(a, (b, a)), min(tuple(b, a)) FROM test;

Response

┌─argMin(a, tuple(b, a))─┬─min(tuple(b, a))─┐
│ d                      │ (NULL,NULL)      │
└────────────────────────┴──────────────────┘

​argMin

argMin