MAX_BY and MIN_BY - Hologres - Alibaba Cloud Documentation Center

Hologres V1.3.36 and later support the MAX_BY and MIN_BY functions. You can use the MAX_BY or MIN_BY function to find the row in which the maximum or minimum value of a column (column y) is included and return the value of another column (column x) in the same row. This topic describes the syntax and limits of the MAX_BY and MIN_BY functions. This topic also describes how to use the MAX_BY and MIN_BY functions.

Limits

Only Hologres instances of V1.3.36 and later support the MAX_BY and MIN_BY functions. If the version of your Hologres instance is earlier than V1.3.36, join a DingTalk group to apply for an instance upgrade. For more information about how to join a Hologres DingTalk group, see Obtain online support for Hologres.

Syntax

The MAX_BY or MIN_BY function is used to find the row in which the maximum or minimum value of a column (column y) is included and return the value of another column (column x) in the same row. Syntax:

Note

Values of the numeric type are sorted based on the numeric values, and values of non-numeric types are sorted in alphabetical order.

MAX_BY(x, y);
MIN_BY(x, y);

The following table describes the parameters used in the syntax.

Parameter

Description

The name of a column whose values you want to sort.

The name of another column whose value you want to query.

If you specify to find the row in which the maximum value of column y is included, the MAX_BY function returns the value of column x in the same row.
If you specify to find the row in which the minimum value of column y is included, the MIN_BY function returns the value of column x in the same row.

Take note of the following items when you use the MAX_BY and MIN_BY functions:

If multiple rows in column y contain the maximum value, the MAX_BY function returns the maximum value of the values in column x in the same rows of column y.
If multiple rows in column y contain the minimum value, the MIN_BY function returns the minimum value of the values in column x in the same rows of column y.
Null values in column y are not involved in the computing. If all values in column y are null, the functions return NULL.

Sample statements

DROP TABLE IF EXISTS test;
CREATE TABLE IF NOT EXISTS test (
    id int,
    name text,
    cost int
);
INSERT INTO test
    VALUES (1, 'a', 100), (1, 'aa', 200), (1, 'aaa', 300), (2, 'b', 150), (2, 'bb', 300), (3, 'c', 150), (3, 'cc', 50);

Examples

Perform a GROUP BY operation on the column named id to group data. Then, use the MAX_BY function to find the row in which the maximum value of the column named cost is included in each group and return the value of the column named name in the same row. For example, you can execute the following statement:
```
SELECT id, max_by(name, cost) FROM test GROUP BY id;

 id | max_by
----+--------
  2 | bb
  1 | aaa
  3 | c
(3 rows)
```
Use the MAX_BY function to find the rows in which the maximum value of the cost column is included and return the maximum value of the values in the name column in the same rows. For example, you can execute the following statement:
```
select max_by(name, cost) from test;

 max_by
--------
 bb
(1 row)
```
Use the MIN_BY function to find the row in which the minimum value of the cost column is included and return the value of the name column in the same row. For example, you can execute the following statement:
```
SELECT min_by(name, cost) FROM test;

 min_by
--------
 cc
(1 row)
```