Caché SQL Reference
An aggregate function that returns the maximum data value in a specified column.
MAX([ALL | DISTINCT [BY(col-list)]] expression [%FOREACH(col-list)] [%AFTERHAVING])
||Optional Applies the aggregate function to all values. ALL has no effect on the value returned by MAX. It is provided for SQL-92 compatibility.
||Optional A DISTINCT clause that specifies that each unique value is considered. DISTINCT has no effect on the value returned by MAX. It is provided for SQL-92 compatibility.
||Any valid expression. Usually the name of a column that contains the values from which the maximum value is to be returned.
||Optional A column name or a comma-separated list of column names. See SELECT for further information on %FOREACH.
||Optional Applies the condition found in the HAVING clause.
The MAX aggregate function
returns the largest (maximum) of the values of expression
. Commonly, expression
is the name of a field, (or an expression containing one or more field names) in the multiple rows returned by a query.
can be used in a SELECT
query or subquery that references either a table or a view. MAX
can appear in a SELECT list or HAVING clause alongside ordinary field values.
cannot be used in a WHERE clause. MAX
cannot be used in the ON clause of a JOIN, unless the SELECT is a subquery.
Like most other aggregate functions, MAX
cannot be applied to a stream field
. Attempting to do so generates an SQLCODE -37 error.
Unlike most other aggregate functions, the ALL and DISTINCT keywords, including MAX(DISTINCT BY(col2) col1)
, perform no operation in MAX
. They are provided for SQL92 compatibility.
The specified field used by MAX
can be numeric or nonnumeric. For a numeric data type field, maximum is defined as highest in numeric value; thus -3 is higher than -7. For a non-numeric data type field, maximum is defined as highest in string collation sequence
; thus '-7' is higher than '-3'.
An empty string ('') value is treated as CHAR(0).
When the field’s defined collation type is SQLUPPER, MAX
returns strings in all uppercase letters. Thus SELECT MAX(Name)
returns 'ZWIG', regardless of the original lettercase of the data. But because comparisons are performed using uppercase collation, the clause HAVING Name=MAX(Name)
selects rows with the Name value 'Zwig', 'ZWIG', and 'zwig'.
For numeric values, the scale returned is the same as the expression
NULL values in data fields are ignored when deriving a MAX
aggregate function value. If no rows are returned by the query, or the data field value for all rows returned is NULL, MAX
Changes Made During the Current Transaction
Like all aggregate functions, MAX
always returns the current state of the data, including uncommitted changes, regardless of the current transaction’s isolation level. For further details, refer to SET TRANSACTION
and START TRANSACTION
The following query returns the highest (maximum) salary in the Sample.Employee database:
SELECT '$' || MAX(Salary) As TopSalary
The following query returns one row for each state that contains at least one employee with a salary smaller than $25,000. Using the %AFTERHAVING keyword, each row returns the maximum employee salary smaller than $25,000. Each row also returns the minimum salary and the maximum salary for all employees in that state:
'$' || MAX(Salary %AFTERHAVING) AS MaxSalaryBelow25K,
'$' || MIN(Salary) AS MinSalary,
'$' || MAX(Salary) AS MaxSalary
GROUP BY Home_State
HAVING Salary < 25000
ORDER BY Home_State
The following query returns the lowest (minimum) and highest (maximum) name in collation sequence found in the Sample.Employee database:
Note that MIN
convert Name values to uppercase before comparison.
The following query returns the highest (maximum) salary for an employee whose Home_State is 'VT' in the Sample.Employee database:
WHERE Home_State = 'VT'
The following query returns the number of employees and the highest (maximum) employee salary for each Home_State in the Sample.Employee database:
COUNT(Home_State) As NumEmployees,
MAX(Salary) As TopSalary
GROUP BY Home_State
ORDER BY TopSalary