argMax

Alias: max_by

Introduced in: v1.1.0

Calculates the arg value for a maximum 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 max 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

argMax(arg, val)

Aliases: max_by

Arguments

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

Returned value

Returns the arg value that corresponds to maximum val value. Type matches arg type.

Examples

Basic usage

CREATE TABLE salary
(
    user String,
    salary UInt32
)
ENGINE = Memory AS
SELECT *
FROM VALUES(('worker', 1000), ('manager', 3000), ('director', 5000));

SELECT argMax(user, salary) FROM salary;

┌─argMax(user, salary)─┐
│ director             │
└──────────────────────┘

Extended example with NULL handling

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

SELECT argMax(a, b), max(b) FROM test;

┌─argMax(a, b)─┬─max(b)─┐
│ b            │      3 │
└──────────────┴────────┘

Using Tuple in arguments

SELECT argMax(a, (b,a)) FROM test;

┌─argMax(a, (b, a))─┐
│ c                 │
└───────────────────┘