Syntax
Purpose
GREATEST returns the greatest of a list of one or more expressions. Oracle Database uses the firstexpr to determine the return type. If the firstexpr is numeric, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type before the comparison, and returns that data type. If the firstexpr is not numeric, then eachexpr after the first is implicitly converted to the data type of the firstexpr before the comparison.
Oracle Database compares eachexpr using nonpadded comparison semantics. The comparison is binary by default and is linguistic if theNLS_COMP parameter is set toLINGUISTIC and theNLS_SORT parameter has a setting other thanBINARY. Character comparison is based on the numerical codes of the characters in the database character set and is performed on whole strings treated as one sequence of bytes, rather than character by character. If the value returned by this function is character data, then its data type isVARCHAR2 if the firstexpr is a character data type andNVARCHAR2 if the firstexpr is a national character data type.
See Also:
"Data Type Comparison Rules" for more information on character comparison
Table 2-8 for more information on implicit conversion and"Floating-Point Numbers" for information on binary-float comparison semantics
"LEAST", which returns the least of a list of one or more expressions
Appendix C inOracle Database Globalization Support Guide for the collation determination rules, which define the collation
GREATESTuses to compare character values forexpr, and for the collation derivation rules, which define the collation assigned to the return value of this function when it is a character value
Examples
The following statement selects the string with the greatest value:
SELECT GREATEST('HARRY', 'HARRIOT', 'HAROLD') "Greatest" FROM DUAL; Greatest--------HARRYIn the following statement, the first argument is numeric. Oracle Database determines that the argument with the highest numeric precedence is the second argument, converts the remaining arguments to the data type of the second argument, and returns the greatest value as that data type:
SELECT GREATEST (1, '3.925', '2.4') "Greatest" FROM DUAL;Greatest-------- 3.925