argAndMax

Introduced in: v1.1.0

Calculates the arg and val value for a maximum val value. If there are multiple rows with equal val being the maximum, which of the associated arg and val 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

argMax
Tuple

Syntax

argAndMax(arg, val)

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 a tuple containing the arg value that corresponds to maximum val value and the maximum val value. Tuple

Examples

Basic usage

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

SELECT argAndMax(user, salary) FROM salary;

┌─argAndMax(user, salary)─┐
│ ('director',5000)       │
└─────────────────────────┘

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), argAndMax(a, b), max(b) FROM test;

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

Using Tuple in arguments

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

┌─argAndMax(a, (b, a))─┐
│ ('c',(2,'c'))        │
└──────────────────────┘

See also

argMax
Tuple