Caché MultiValue Query Language (CMQL) Reference
MAX clause
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

Returns the maximum value of the values of a field.
Synopsis
MAX field [GRAND.TOTAL "label"]
Description
The MAX clause returns the maximum numeric value for a field. If a WITH (or other conditional clause) is specified, MAX returns the largest value of the values of that field that pass the condition test.
The following example lists all of the values of the AMOUNT field, then returns the largest of these values:
LIST SALES AMOUNT MAX AMOUNT
The following example lists all of the values of the @ID field that pass the condition test, then returns the largest of these values:
LIST SALES MAX @ID WITH @ID > 24990
It lists a result set such as the following:
SALES..... SALES.....
 
24991      24991
24992      24992
24993      24993
24994      24994
24995      24995
24996      24996
24997      24997
24998      24998
24999      24999
25000      25000
 
***        25000 
 
10 Items listed.
The MAX value is listed after the detail listing, using the same format as a TOTAL value.
You can use the DET-SUPP keyword, or the (D letter code, to suppress listing individual values, as follows:
LIST SALES MAX @ID WITH @ID > 24990 DET-SUPP
Which returns:
SALES..... SALES.....
 
***        25000 
 
10 Items listed.
The following example lists the total, maximum, and minimum values:
LIST SALES TOTAL @ID MAX @ID MIN @ID WITH @ID > 24990
It lists a result set such as the following:
SALES..... SALES..... SALES..... SALES.....
 
24991      24991      24991      24991
24992      24992      24992      24992
24993      24993      24993      24993
24994      24994      24994      24994
24995      24995      24995      24995
24996      24996      24996      24996
24997      24997      24997      24997
24998      24998      24998      24998
24999      24999      24999      24999
25000      25000      25000      25000
 
***        249955     25000      24991

10 Items listed.
Note that the maximum, minimum, and total values are all listed in the same summation line in the order in which the clauses were specified. You can clarify what these values are using the GRAND.TOTAL keyword.
GRAND.TOTAL Keyword
GRAND.TOTAL and GRAND-TOTAL are synonyms.
By default, in a horizontal display the summation line is not labeled; it is indicated by the “***” placeholder. You can use the GRAND.TOTAL keyword to assign a label name to this placeholder. GRAND.TOTAL has no effect on vertical display format.
The following example uses GRAND.TOTAL to label the summation line. Note that GRAND.TOTAL does not have to directly follow the clause(s) that it affects.
LIST SALES TOTAL @ID MAX @ID MIN @ID WITH @ID > 24990 GRAND.TOTAL "Sum/Max/Min"
It returns:
SALES..... SALES..... SALES..... SALES.....
 
24991      24991      24991      24991
24992      24992      24992      24992
24993      24993      24993      24993
24994      24994      24994      24994
24995      24995      24995      24995
24996      24996      24996      24996
24997      24997      24997      24997
24998      24998      24998      24998
24999      24999      24999      24999
25000      25000      25000      25000
 
Sum/Max/Min 249955     25000      24991

10 Items listed.
See Also