argMax
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.
Syntax
argMax(arg, val)
Arguments
arg
— Argument.val
— Value.
Returned value
arg
value that corresponds to maximumval
value.
Type: matches arg
type.
Example
Input table:
┌─user─────┬─salary─┐
│ director │ 5000 │
│ manager │ 3000 │
│ worker │ 1000 │
└──────────┴────────┘
Query:
SELECT argMax(user, salary) FROM salary;
Result:
┌─argMax(user, salary)─┐
│ director │
└──────────────────────┘
Extended example
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 * from test;
┌─a────┬────b─┐
│ a │ 1 │
│ b │ 2 │
│ c │ 2 │
│ ᴺᵁᴸᴸ │ 3 │
│ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │
│ d │ ᴺᵁᴸᴸ │
└──────┴──────┘
SELECT argMax(a, b), max(b) FROM test;
┌─argMax(a, b)─┬─max(b)─┐
│ b │ 3 │ -- argMax = 'b' because it the first not Null value, max(b) is from another row!