InterSystems SQL Reference
GREATEST


A function that returns the greatest value from a list of values.
Synopsis
GREATEST(expression,expression[,...])
Arguments
Description
GREATEST returns the greatest value from a commaseparated list of expression values. Expressions are evaluated in lefttoright order. If only one expression is provided, GREATEST returns that value. If any expression is NULL, GREATEST returns NULL.
If all of the expression values resolve to canonical numbers, they are compared in numeric order. If a quoted string contains a number in canonical format, it is compared in numeric order. However, if a quoted string contains a number not in canonical format (for example, '00', '0.4', or '+4'), it is compared as a string. String comparisons are performed characterbycharacter in collation order. Any string value is greater than any numeric value.
The empty string is greater than any numeric value, but less than any other string value.
If the returned value is a number, GREATEST returns it in canonical format (leading and trailing zeros removed, etc.). If the returned value is a string, GREATEST returns it unchanged, including any leading or trailing blanks.
The inverse function of
GREATEST is
LEAST.
Data Type of Returned Value
If the data types of the
expression values are different, the data type returned is the type most compatible with all of the possible return values, the data type with the highest
data type precedence. For example, if one
expression is an integer and another
expression is a fractional number,
GREATEST returns a value with data type NUMERIC. This is because NUMERIC is the data type with the highest precedence that is compatible with both. If, however, an
expression is a literal number or string,
GREATEST returns data type VARCHAR.
Examples
In the following example, each GREATEST compares three canonical numbers:
SELECT GREATEST(22,2.2,21) AS HighNum,
GREATEST('2.2','22','21') AS HighNumStr
In the following example, each GREATEST compare three numeric strings. However, each GREATEST contains one string that is noncanonical; these noncanonical values are compared as character strings. A character string is always greater than a number:
SELECT GREATEST('22','+2.2','21'),
GREATEST('0.2','22','21')
In the following example, each GREATEST compare three strings and returns the value with the highest collation sequence:
SELECT GREATEST('A','a',''),
GREATEST('a','ab','abc'),
GREATEST('#','0','7'),
GREATEST('##','00','77')
The following example compares two dates, treated as canonical numbers: the date of birth as a $HOROLOG integer, and the integer 58073 converted to a date. It returns the date of birth for each person born in the 21st century. Anyone born before January 1, 2000 is displayed with the default birth date of December 31, 1999:
SELECT Name,GREATEST(DOB,TO_DATE(58073)) AS NewMillenium
FROM Sample.Person
See Also
Content Date/Time: 20191018 06:48:42