[CALCITE-5283] Add ARG_MIN, ARG_MAX (aka MIN_BY, MAX_BY) aggregate functions

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.33.0
• Labels: pull-request-available
• Assignee: Dan Zou
• Reporter: Julian Hyde
• Created: 13/Sep/22 01:40
• Updated: 07/Feb/23 22:33
• Resolved: 26/Jan/23 00:49

Description

Add ARG_MIN, ARG_MAX (aka MIN_BY, MAX_BY) aggregate functions.

For example, the following query prints the name of the lowest paid employee in each department:

SELECT deptno, ARG_MIN(sal, ename)
FROM emp
GROUP BY deptno;

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | MILLER     |
| 20         | SMITH      |
| 30         | JAMES      |
+------------+------------+

If multiple employees have the same salary, arbitrarily chooses one to return.

See Yandex description of ARG_MIN. Also implemented in Alibaba MaxCompute, Clickhouse, DuckDB. (And in Alibaba MaxCompute, Spark SQL, Firebolt, PrestoDB, Trino as MIN_BY/MAX_BY.)

Issue Links

• links to: GitHub Pull Request #2981