The MIN function returns the minimum value of a specified column.
Syntax
MIN([ DISTINCT | UNIQUE | ALL ] expr) [ OVER (analytic_clause) ]
If the function is used as an analytic function, you must use the full syntax of a window function. The function calculates a set of rows and returns multiple values. If the function is used as an aggregate function, the function aggregates a set of rows and returns only one value. In this case, you do not need to add the OVER keyword.
Parameters
| Parameter | Description |
|---|---|
| DISTINCT | Removes duplicate rows from returned rows and ignores the rows whose values are NULL. |
| UNIQUE | Removes duplicate rows from returned rows and ignores the rows whose values are NULL. |
| ALL | Returns all the values, including duplicate rows, and ignores the rows whose values are NULL. |
| expr | The data column or expression of the numeric type, character type, date type, or other types. |
| OVER | Uses the OVER clause to define a window for calculation. |
Return type
The value of the data type same as the data type of expr is returned.
Examples
Examples of the analytic function
The following statements create the employees table and insert data into the table:
CREATE TABLE employees (manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(100, 'De Haan', '2018-05-01',11000);
INSERT INTO employees VALUES(100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(100, 'Hartstein', '2019-05-01',14000);
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-22', 1700);
INSERT INTO employees VALUES(100, 'Weiss', '2019-07-11',13500);
INSERT INTO employees VALUES(100, 'Russell', '2019-10-05', 13000);
INSERT INTO employees VALUES(100, 'Partners', '2018-12-01',14000);
INSERT INTO employees VALUES(200, 'Ross', '2019-06-11',13500);
INSERT INTO employees VALUES(200, 'Bell', '2019-05-25', 13000);
INSERT INTO employees VALUES(200, 'Part', '2018-08-11',14000);
Execute the following statement to query the minimum value of the SALARY column:
SELECT manager_id, last_name, hiredate, salary, MIN(salary) OVER(PARTITION BY manager_id
ORDER BY hiredate RANGE UNBOUNDED PRECEDING) AS p_cmin
FROM employees ORDER BY manager_id, last_name, hiredate, salary;
COMMIT;
The following query result is returned:
+------------+-----------+------------+--------+--------+
| MANAGER_ID | LAST_NAME | HIREDATE | SALARY | P_CMIN |
+------------+-----------+------------+--------+--------+
| 100 | De Haan | 2018-05-01 | 11000 | 1400 |
| 100 | Errazuriz | 2017-07-21 | 1400 | 1400 |
| 100 | Hartstein | 2019-05-01 | 14000 | 1400 |
| 100 | Partners | 2018-12-01 | 14000 | 1400 |
| 100 | Raphaely | 2017-07-01 | 1700 | 1700 |
| 100 | Raphaely | 2017-07-22 | 1700 | 1400 |
| 100 | Russell | 2019-10-05 | 13000 | 1400 |
| 100 | Weiss | 2019-07-11 | 13500 | 1400 |
| 200 | Bell | 2019-05-25 | 13000 | 13000 |
| 200 | Part | 2018-08-11 | 14000 | 14000 |
| 200 | Ross | 2019-06-11 | 13500 | 13000 |
+------------+-----------+------------+--------+--------+
Examples of the aggregate function
Execute the following statement to query the minimum value of the SALARY column:
SELECT MIN(salary) FROM employees ;
The following query result is returned:
+-------------+
| MIN(SALARY) |
+-------------+
| 1400 |
+-------------+
