# 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 maximum val 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 ASSELECT *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!└──────────────┴────────┘SELECT argMax(tuple(a), b) FROM test;┌─argMax(tuple(a), b)─┐│ (NULL)              │ -- The a Tuple that contains only a NULL value is not NULL, so the aggregate functions won't skip that row because of that NULL value└─────────────────────┘SELECT (argMax((a, b), b) as t).1 argMaxA, t.2 argMaxB FROM test;┌─argMaxA─┬─argMaxB─┐│ ᴺᵁᴸᴸ    │       3 │ -- you can use Tuple and get both (all - tuple(*)) columns for the according max(b)└─────────┴─────────┘SELECT argMax(a, b), max(b) FROM test WHERE a IS NULL AND b IS NULL;┌─argMax(a, b)─┬─max(b)─┐│ ᴺᵁᴸᴸ         │   ᴺᵁᴸᴸ │ -- All aggregated rows contains at least one NULL value because of the filter, so all rows are skipped, therefore the result will be NULL└──────────────┴────────┘SELECT argMax(a, (b,a)) FROM test;┌─argMax(a, tuple(b, a))─┐│ c                      │ -- There are two rows with b=2, Tuple in the Max allows to get not the first arg└────────────────────────┘SELECT argMax(a, tuple(b)) FROM test;┌─argMax(a, tuple(b))─┐│ b                   │ -- Tuple can be used in Max to not skip Nulls in Max└─────────────────────┘