Purpose
This function returns the minimum value of the specified column in the parameters. It can be used as an aggregate or analytic function.
Note
- When used as an analytic function, you must use the
OVERclause to define the window for computation. It computes over a set of rows and returns multiple values. - When used as an aggregate function, this function computes an aggregate over a set of rows and returns a single value. In this case, the
OVERclause is not required.
Syntax
MIN([ DISTINCT | UNIQUE | ALL ] expr) [ OVER (analytic_clause) ]
Parameters
Parameter |
Description |
|---|---|
| DISTINCT | UNIQUE | ALL | Specifies whether to remove duplicates. Default value is ALL.
|
| expr | A column or expression of numeric, character, date, or other data type. |
| OVER | Defines the window for computation using the OVER clause. For more information, see Analytic Function Description. |
Return type
The function returns a value of the same data type as expr.
Examples
Assume that the employees table has been created.
obclient> SELECT * FROM employees;
+---------------+------------+-----------+------------+--------+
| DEPARTMENT_ID | MANAGER_ID | LAST_NAME | HIREDATE | SALARY |
+---------------+------------+-----------+------------+--------+
| 30 | 100 | Raphaely | 2017-07-01 | 1700 |
| 30 | 100 | De Haan | 2018-05-01 | 11000 |
| 40 | 100 | Errazuriz | 2017-07-21 | 1400 |
| 50 | 100 | Hartstein | 2019-10-05 | 14000 |
| 50 | 100 | Raphaely | 2017-07-22 | 1700 |
| 50 | 100 | Weiss | 2019-10-05 | 13500 |
| 90 | 100 | Russell | 2019-07-11 | 13000 |
| 90 | 100 | Partners | 2018-12-01 | 14000 |
+---------------+------------+-----------+------------+--------+
8 rows in set
Aggregate function example
Query the minimum value of the salary column.
obclient> SELECT MIN(salary) FROM employees;
+-------------+
| MIN(SALARY) |
+-------------+
| 1400 |
+-------------+
1 row in set
Analytic function example
Group by the department_id column and query the minimum value of the salary column.
obclient> SELECT department_id,last_name,hiredate,salary,
MIN(salary) OVER(PARTITION BY department_id) AS p_cmin
FROM employees;
+---------------+-----------+------------+--------+--------+
| DEPARTMENT_ID | LAST_NAME | HIREDATE | SALARY | P_CMIN |
+---------------+-----------+------------+--------+--------+
| 30 | Raphaely | 2017-07-01 | 1700 | 1700 |
| 30 | De Haan | 2018-05-01 | 11000 | 1700 |
| 40 | Errazuriz | 2017-07-21 | 1400 | 1400 |
| 50 | Hartstein | 2019-10-05 | 14000 | 1700 |
| 50 | Raphaely | 2017-07-22 | 1700 | 1700 |
| 50 | Weiss | 2019-10-05 | 13500 | 1700 |
| 90 | Russell | 2019-07-11 | 13000 | 13000 |
| 90 | Partners | 2018-12-01 | 14000 | 13000 |
+---------------+-----------+------------+--------+--------+
8 rows in set