Categories:

Aggregate Functions (General)

MIN_BY / MAX_BY¶

Finds the row(s) containing the minimum or maximum value for a column and returns the value of another column in that row.

For example, if a table contains the columns employee_id and salary, MIN_BY(employee_id, salary) returns the value of the employee_id column for the row that has the lowest value in the salary column.

If multiple rows contain the specified minimum or maximum value, the function is non-deterministic.

To return values for multiple rows, specify the optional maximum_number_of_values_to_return argument. With this additional argument:

  • The function returns an ARRAY containing the values of a column for the rows with the lowest or highest values of a specified column.

  • The values in the ARRAY are sorted by their corresponding values in the column containing the minimum or maximum values.

  • If multiple rows contain these lowest or highest values, the function is non-deterministic.

For example, MIN_BY(employee_id, salary, 5) returns an ARRAY of values of the employee_id column for the 5 rows containing the lowest values in the salary column. The IDs in the ARRAY are sorted by the corresponding values in the salary column.

See also

MIN / MAX

Syntax¶

MIN_BY( <col_to_return>, <col_containing_mininum> [ , <maximum_number_of_values_to_return> ] )
Copy
MAX_BY( <col_to_return>, <col_containing_maximum> [ , <maximum_number_of_values_to_return> ] )
Copy

Arguments¶

Required:

col_to_return

Column containing the value to return.

col_containing_mininum , . col_containing_maximum

Column containing the minimum or maximum value.

Optional:

maximum_number_of_values_to_return

Constant integer specifying the maximum number of values to return. You must specify a positive number. The maximum number that you can specify is 1000.

Returns¶

  • If maximum_number_of_values_to_return is not specified, the function returns a value of the same type as col_to_return.

  • If maximum_number_of_values_to_return is specified, the function returns an ARRAY containing values of the same type as col_to_return. The values in the ARRAY are sorted by their corresponding col_containing_mininum / col_containing_maximum values.

    For example, MIN_BY(employee_id, salary, 5) returns the IDs of the employees with the lowest 5 salaries, sorted by salary (in descending order).

Usage Notes¶

  • The function ignores NULL values in col_containing_mininum and col_containing_maximum.

  • If all values in col_containing_mininum or col_containing_maximum are NULL, the function returns NULL (regardless of whether the optional maximum_number_of_values_to_return argument is specified).

Examples¶

The following examples demonstrate how to use the MIN_BY and MAX_BY functions.

To run these examples, execute the following statements to set up the table and data for the examples:

CREATE OR REPLACE TABLE employees(employee_id NUMBER, department_id NUMBER, salary NUMBER);

INSERT INTO employees VALUES
  (1001, 10, 10000),
  (1020, 10, 9000),
  (1030, 10, 8000),
  (900, 20, 15000),
  (2000, 20, NULL),
  (2010, 20, 15000);
Copy

Execute the following statement to view the contents of this table:

SELECT * FROM employees;

+-------------+---------------+--------+
| EMPLOYEE_ID | DEPARTMENT_ID | SALARY |
|-------------+---------------+--------|
|        1001 |            10 |  10000 |
|        1020 |            10 |   9000 |
|        1030 |            10 |   8000 |
|         900 |            20 |  15000 |
|        2000 |            20 |   NULL |
|        2010 |            20 |  15000 |
+-------------+---------------+--------+
Copy

The following example returns the IDs of the employees with the highest and lowest salaries:

SELECT MIN_BY(employee_id, salary), MAX_BY(employee_id, salary) from employees;

+-----------------------------+-----------------------------+
| MIN_BY(EMPLOYEE_ID, SALARY) | MAX_BY(EMPLOYEE_ID, SALARY) |
|-----------------------------+-----------------------------|
|                        1030 |                         900 |
+-----------------------------+-----------------------------+
Copy

Note the following:

  • Because more than one row contains the maximum value for the salary column, the function is non-deterministic and might return the employee ID for a different row in subsequent executions.

  • The function ignores the NULL value in the salary column when determining the rows with the minimum and maximum values.

The following example returns an ARRAY containing the IDs of the employees with the 3 highest and lowest salaries:

SELECT MIN_BY(employee_id, salary, 3), MAX_BY(employee_id, salary, 3) from employees;

+--------------------------------+--------------------------------+
| MIN_BY(EMPLOYEE_ID, SALARY, 3) | MAX_BY(EMPLOYEE_ID, SALARY, 3) |
|--------------------------------+--------------------------------|
| [                              | [                              |
|   1030,                        |   900,                         |
|   1020,                        |   2010,                        |
|   1001                         |   1001                         |
| ]                              | ]                              |
+--------------------------------+--------------------------------+
Copy

As shown in the example, the values in the ARRAY are sorted by their corresponding values in the salary column:

  • MIN_BY returns the IDs of employees sorted by their salary in ascending order.

  • MAX_BY returns the IDs of employees sorted by their salary in descending order.

If more than one of these rows contain the same value in the salary column, the order of the returned values for that salary is non-deterministic.

Language: English