Skip to main content
Last updated on

max_by aggregate function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns the value of an expression associated with the largest value of a second expression in a group. With the optional third argument, returns an array of up to limit values corresponding to the largest values of the ordering expression.

Syntax​

max_by(expr, ordExpr) [FILTER ( WHERE cond ) ]

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 18.1 and above

max_by(expr, ordExpr, limit) [FILTER ( WHERE cond ) ]

This function can also be invoked as a window function using the OVER clause.

Arguments​

  • expr: The expression whose value is to be returned. Can be any data type.
  • ordExpr: The expression used to determine the maximum value (the sorting key). Must be an orderable type.
  • limit: (Optional) An INT value representing the maximum number of values to return. Must be greater than 0 and less than or equal to 100,000. When provided, the function returns an array of up to limit values.
  • cond: An optional boolean expression filtering the rows used for aggregation.

Returns​

Without limit: the result type matches the type of expr. If multiple rows have the same maximum value of ordExpr, the result is non-deterministic.

With limit: an ARRAY of values whose element type matches the type of expr. The array contains up to limit elements. Values corresponding to NULL orderings are not included. If all ordering values are NULL, the result is NULL. If multiple rows share the same largest values, the function is non-deterministic.

note

For certain STRING collations, such as UTF8_LCASE, the result may be non-deterministic as well.

Examples​

The following examples define their input rows with an inline table, so you can run them without creating a table. In FROM VALUES ... AS tab(x, y), tab is the table alias and x and y are its column names. See VALUES clause.

SQL
-- Return the value of x for the row with the largest y
> SELECT max_by(x, y) FROM VALUES (('a', 10)), (('b', 50)), (('c', 20)) AS tab(x, y);
 b

-- Return the values of x for the rows with the 2 largest y values
> SELECT max_by(x, y, 2) FROM VALUES (('a', 10)), (('b', 50)), (('c', 20)) AS tab(x, y);
 [b, c]

-- Return the highest-paid employee in each department
> SELECT department, max_by(name, salary)
    FROM VALUES ('Alice', 'Engineering', 150000), ('Bob', 'Engineering', 120000), ('Carol', 'Engineering', 135000), ('Dan', 'Sales', 90000), ('Eve', 'Sales', 110000) AS employees(name, department, salary)
    GROUP BY department ORDER BY department;
  Engineering Alice
  Sales       Eve

-- Return the 2 highest-paid employees in each department
> SELECT department, max_by(name, salary, 2)
    FROM VALUES ('Alice', 'Engineering', 150000), ('Bob', 'Engineering', 120000), ('Carol', 'Engineering', 135000), ('Dan', 'Sales', 90000), ('Eve', 'Sales', 110000) AS employees(name, department, salary)
    GROUP BY department ORDER BY department;
  Engineering [Alice, Carol]
  Sales       [Eve, Dan]

> SELECT max_by(x, y COLLATE UTF8_LCASE) FROM VALUES (('a', 'X')), (('b', 'x')), (('c', 'v')) AS tab(x, y);
 a (or b)
On this page