Calculates the minimum value.
Limits
Before you use window functions, take note of the following limits:
Window functions are supported only in
SELECTstatements.A window function cannot contain nested window functions or nested aggregate functions.
You cannot use window functions together with aggregate functions of the same level.
Syntax
--Calculate the minimum value
min(<colname>)
--Calculate the minimum value of expr in a window
min(<expr>) over([partition_clause] [orderby_clause] [frame_clause])Description
Returns the minimum value of a column or the minimum value of expr in a window.
Parameters
colname: Required. The name of a column, which can be of any data type other than BOOLEAN.
expr: Required. The expression used to calculate the minimum value. The input value can be of any data type other than BOOLEAN. If the value for a row is NULL, this row is not used for calculation.
partition_clause, orderby_clause, and frame_clause: For more information, see windowing_definition.
Return value
The return value is of the same type as colname. The return value varies based on the following rules:
If the value of colname is NULL, this row is not used for calculation.
If colname is of the BOOLEAN type, it cannot be used for calculation.
The return value is of the same type as expr.
Sample data
This section provides sample source data and examples for you to understand how to use the functions. Create a table named emp and insert the sample data into the table. Sample statement:
create table if not exists emp
(empno bigint,
ename string,
job string,
mgr bigint,
hiredate datetime,
sal bigint,
comm bigint,
deptno bigint);
tunnel upload emp.txt emp; -- Replace emp.txt with the actual path (path and name) to which you upload the data file.The emp.txt file contains the following sample data:
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10Command examples
Example 1: Use the deptno column to define a window and obtain the minimum value of the sal column. The ORDER BY clause is not specified. This function returns the minimum value of the current window. The current window includes the rows that have the same deptno value. Sample command:
SELECT deptno, sal, MIN(sal) OVER (partition by deptno) FROM emp;The following figure shows the returned result:
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300 | -- This row is the first row of this window. The return value is the minimum value among the values from the first row to the sixth row. | 10 | 2450 | 1300 | -- The return value is the minimum value among the values from the first row to the sixth row. | 10 | 5000 | 1300 | -- The return value is the minimum value among the values from the first row to the sixth row. | 10 | 1300 | 1300 | | 10 | 5000 | 1300 | | 10 | 2450 | 1300 | | 20 | 3000 | 800 | | 20 | 3000 | 800 | | 20 | 800 | 800 | | 20 | 1100 | 800 | | 20 | 2975 | 800 | | 30 | 1500 | 950 | | 30 | 950 | 950 | | 30 | 1600 | 950 | | 30 | 1250 | 950 | | 30 | 1250 | 950 | | 30 | 2850 | 950 | +------------+------------+------------+Example 2: Use the deptno column to define a window and obtain the minimum value of the sal column. The ORDER BY clause is specified. This function returns the minimum value among the values from the first row to the current row in the current window. The current window includes the rows that have the same deptno value. Sample command:
SELECT deptno, sal, MIN(sal) OVER (PARTITION BY deptno ORDER BY sal) FROM emp;The following figure shows the returned result:
+------------+------------+------------+ | deptno | sal | _c2 | +------------+------------+------------+ | 10 | 1300 | 1300 | -- This row is the first row of this window. | 10 | 1300 | 1300 | -- The return value is the minimum value among the values in the first and second rows. | 10 | 2450 | 1300 | -- The return value is the minimum value among the values from the first row to the third row. | 10 | 2450 | 1300 | | 10 | 5000 | 1300 | | 10 | 5000 | 1300 | | 20 | 800 | 800 | | 20 | 1100 | 800 | | 20 | 2975 | 800 | | 20 | 3000 | 800 | | 20 | 3000 | 800 | | 30 | 950 | 950 | | 30 | 1250 | 950 | | 30 | 1250 | 950 | | 30 | 1500 | 950 | | 30 | 1600 | 950 | | 30 | 2850 | 950 | +------------+------------+------------+Example 3: Calculate the lowest salary (sal) of all employees. Sample command:
SELECT MIN(sal) FROM emp;The following figure shows the returned result:
+------------+ | _c0 | +------------+ | 800 | +------------+Example 4: Use the function with
GROUP BYto group all employees by department (deptno) and calculate the lowest salary (sal) for each department. Sample command:SELECT deptno, MIN(sal) FROM emp GROUP BY deptno;The following figure shows the returned result:
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 1300 | | 20 | 800 | | 30 | 950 | +------------+------------+
Related functions
MIN is an aggregate function or a window function.
For more information about functions that calculate the average value or aggregate parameters from multiple input records, see Aggregate functions.
For more information about functions that sum or sort values in specified window columns, see Window functions.