GREATEST

The GREATEST function returns the greatest of the list of one or more expressions.

SQL syntax

GREATEST (Expression [,...])

Parameters

GREATEST has the parameter:

Parameter Description

Expression [,...]

List of one or more expressions that is evaluated to determine the greatest expression value. Operand or column can be numeric, character or date. Each expression in the list must be from the same data type family.

Description

  • Each expression in the list must be from the same data type family or date subfamily. Data type families include numeric, character and date. The date family includes four subfamilies: date family, TIME family, TT_DATE family, and TT_TIMESTAMP family. As an example, do not specify a numeric expression and a character expression in the list of expressions. Similarly, do not specify a date expression and a TT_TIMESTAMP expression in the list of expressions.

  • If the first Expression is numeric, then TimesTen 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 first Expression is in the character family, and the operand or column is of type CHAR or VARCHAR2, the data type returned is VARCHAR2. If the operand or column is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2. The returned data type length is equal to the length of the largest expression. If one operand or column is of type CHAR or VARCHAR2 and the second operand or column is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2.

  • TimesTen uses nonpadded comparison semantics for data types from the character family.

  • If the first expression is in the date family, the data type returned is the same data type as the first expression.

  • If any of the expressions is NULL, the result is NULL.

  • If the first Expression is in the character family, and the operand or column is of type TT_CHAR or TT_VARCHAR, the data type returned is TT_VARCHAR. If the operand or column is of type TT_NCHAR or TT_NVARCHAR, the data type returned is TT_NVARCHAR. The returned data type length is equal to the largest of the expressions.

  • You can specify a maximum of 256 expressions.

Use the GREATEST function to return the string with the greatest value: 

Command> SELECT GREATEST ('GREAT', 'GREATER', 'GREATEST') FROM dual;
< GREATEST >
1 row found.

Use the GREATEST function to return the numeric expression with the greatest value. In this example, BINARY_DOUBLE is the data type with the highest numeric precedence, so arguments are implicitly converted to BINARY_DOUBLE before the comparison and the data type BINARY_DOUBLE is returned: 

Command> SELECT GREATEST (10, 10.55, 10.1D) FROM dual;
< 10.5500000000000 >
1 row found.

Use the DESCRIBE command to confirm the data type returned is BINARY_DOUBLE: 

Command> DESCRIBE SELECT GREATEST (10, 10.55, 10.1D) FROM dual;

Prepared Statement:
  Columns:
    EXP                             BINARY_DOUBLE NOT NULL

Use the GREATEST function to return the DATE expression with the greatest value. DATE and TIMESTAMP are in the same date family. 

Command> SELECT GREATEST (DATE '2007-09-30',TIMESTAMP '2007-09-30:10:00:00') 
         FROM dual;
< 2007-09-30 10:00:00 >
1 row found.

Attempt to use the GREATEST function to return the greatest value in the list of TT_DATE and TT_TIMESTAMP expressions. You see an error because TT_DATE and TT_TIMESTAMP are in different date subfamilies and cannot be used in the same list of expressions. 

Command> SELECT GREATEST (TT_DATE '2007-09-30', 
                          TT_TIMESTAMP '2007-09-30:10:00:00') 
         FROM dual;
2817: Invalid data type TT_TIMESTAMP for argument 2 for function GREATEST
The command failed.

Use the GREATEST function to return the TT_DATE expression with the greatest value. 

Command> SELECT GREATEST (TT_DATE '2007-09-30',
                          TT_DATE '2007-09-29', 
                          TT_DATE '2007-09-28')
         FROM dual;
< 2007-09-30 >
1 row found.