7.22.5 MIN
MIN determines the minimum value.
- Organization of this subsection
(1) Specification format
general-set-function-MIN ::= {MIN([ALL] value-expression)|MIN(DISTINCT value-expression)}
Note: Whichever form is specified, the results will be the same.
(2) Explanation of specification format
- MIN([ALL] value-expression):
-
Determines the minimum value of the result of the value expression. For details about value expressions, see 7.20 Value expression.
ALL can be omitted. The results will be the same regardless of whether it is specified.
- MIN(DISTINCT value-expression):
-
Determines the minimum value of the results of the value expression. For details about value expressions, see 7.20 Value expression.
(3) Rules
-
Null values are not included in the calculation.
-
In the following cases, the execution result will be a null value:
-
If the number of input rows is 0
-
If the values to be calculated are all null values
-
-
The following table shows the data type that can be specified in the value expression and the data type of the execution result of the general set function MIN.
Table 7‒18: Relationship between data type that can be specified in the value expression and data type of the execution result of the general set function MIN No.
Data type that can be specified in the value expression
Data type of the execution result of general set function MIN
1
INTEGER
INTEGER
2
SMALLINT
SMALLINT
3
DECIMAL(m,n)
DECIMAL(m,n)
4
DOUBLE PRECISION
DOUBLE PRECISION
5
CHARACTER(n)
CHARACTER(n)
6
VARCHAR(n)
VARCHAR(n)
7
DATE
DATE
8
TIME(p)
TIME(p)
9
TIMESTAMP(p)
TIMESTAMP(p)
(4) Examples
- Example 1
-
Using the data in the employee table (EMPLIST), this example determines the age (AGE) of the youngest female employee.
SELECT MIN("AGE") AS "MIN-AGE" FROM "EMPLIST" WHERE "SEX"='F'
Example of execution results
- Example 2
-
Using the data in the employee table (EMPLIST), this example determines the age (AGE) of the youngest employee in each section (SCODE).
SELECT "SCODE",MIN("AGE") AS "MIN-AGE" FROM "EMPLIST" GROUP BY "SCODE"
Example of execution results
- Example 3
-
Using the data in the employee table (EMPLIST), this example determines the ages (AGE) of the oldest employee and the youngest employee in each section where the age difference does not exceed 20 years.
SELECT "SCODE",MAX("AGE") AS "MAX-AGE",MIN("AGE") AS "MIN-AGE" FROM "EMPLIST" GROUP BY "SCODE" HAVING MAX("AGE")-MIN("AGE")<=20
Example of execution results