7.23.4 MAX

(1) Specification format

general-set-function-MAX ::= {MAX([ALL] value-expression)|MAX(DISTINCT value-expression)}

Note: Whichever form is specified, the results will be the same.

To Page Top

(2) Explanation of specification format

MAX([ALL] value-expression):

Determines the maximum value of the results of the value expression. For details about value expressions, see 7.21 Value expression.

ALL can be omitted. The results will be the same regardless of whether it is specified.

MAX(DISTINCT value-expression):

Determines the maximum value of the results of the value expression. For details about value expressions, see 7.21 Value expression.

To Page Top

(3) Rules

1. Null values are not included in the calculation.

2. 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

3. 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 MAX.

   Table 7‒24: Relationship between data type that can be specified in the value expression and data type of the execution result of the general set function MAX

   No.	Data type that can be specified in the value expression	Data type of the execution result of general set function MAX
   1	BIGINT	BIGINT#
   2	INTEGER	INTEGER
   3	SMALLINT	SMALLINT
   4	DECIMAL(m,n)	DECIMAL(m,n)
   5	NUMERIC(m,n)
   6	DOUBLE PRECISION	DOUBLE PRECISION
   7	FLOAT
   8	REAL	REAL
   9	CHARACTER(n)	CHARACTER(n)
   10	VARCHAR(n)	VARCHAR(n)
   11	STRING	STRING
   12	DATE	DATE
   13	TIME(p)	TIME(p)
   14	TIMESTAMP(p) WITHOUT TIME ZONE	TIMESTAMP(p) WITHOUT TIME ZONE
   15	TIMESTAMP(p) WITH TIME ZONE	TIMESTAMP(p) WITH TIME ZONE
   16	BOOLEAN	BOOLEAN

   #

   If the data format of the integer data type is a legacy format, the data type of the execution result will be an INTEGER type.

To Page Top

(4) Examples

Example 1

Using the data in the employee table (EMPLIST), this example determines the age (AGE) of the oldest male employee.

SELECT MAX("AGE") AS "MAX-AGE"
    FROM "EMPLIST"
       WHERE "SEX"='M'

Example of execution results

Example 2

Using the data in the employee table (EMPLIST), this example determines the age (AGE) of the oldest employee in each section (SCODE).

SELECT "SCODE",MAX("AGE") AS "MAX-AGE"
    FROM "EMPLIST"
        GROUP BY "SCODE"

Example of execution results

To Page Top