Oracle GREATEST() Function

Oracle GREATEST() is a built-in function that returns the maximum value from a given list of parameters.

If you want to obtain the minimum value from the list of parameters, use LEAST().

Oracle GREATEST() Syntax

Here is the syntax for the Oracle GREATEST() function:

GREATEST(expr [, expr ]...)

Parameters

expr [, expr ]...

Required. The list of parameters used for comparison. All parameters participate in the comparison. Parameters can be any data type or expression. You should provide at least one parameter.

Return Value

The Oracle GREATEST() function returns the maximum value from the given list of parameters.

The Oracle database uses the first expr to determine the return type. If the first expr is a number, then Oracle determines the parameter with the highest numeric precedence, implicitly converts all other parameters to that data type before comparison, and returns that data type. If the first expr is not a number, then the second and subsequent exprs are implicitly converted to the data type of the first expr before comparison.

If any parameter is NULL, GREATEST() will return NULL.

Oracle GREATEST() Examples

Here are some examples demonstrating the usage of the Oracle GREATEST() function.

Numeric

To obtain the maximum value from a list of numbers, use the following statement:

SELECT
    GREATEST(1, 4, 2, 5, 3)
FROM dual;

Output:

   GREATEST(1,4,2,5,3)
______________________
                     5

String

The MariaDB GREATEST() function supports strings as parameters and returns the maximum string.

SELECT
    GREATEST('abc', 'hello', 'good')
FROM dual;

Output:

GREATEST('ABC','HELLO','GOOD')
_________________________________
hello

Date

The MariaDB GREATEST() function allows you to obtain the maximum value from a set of dates.

SELECT
    GREATEST(DATE '2023-01-31', DATE '2023-01-01') Result
FROM dual;

Output:

RESULT
___________
31-JAN-23

NULL Parameters

If any parameter is NULL, GREATEST() will return NULL.

SET NULL 'NULL';
SELECT
    GREATEST(NULL, NULL) Result1,
    GREATEST('ab', NULL) Result2,
    GREATEST(NULL, 'ab') Result3
FROM dual;

Output:

RESULT1    RESULT2    RESULT3
__________ __________ __________
NULL       NULL       NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

The Oracle GREATEST() function is a built-in function that returns the maximum value from a given list of parameters.