GREATEST() Function in Oracle

by

In Oracle Database, the GREATEST() function returns the greatest of a list of one or more expressions.

Syntax

The syntax goes like this:

GREATEST(expr [, expr ]...)

The first expr is used to determine the return type:

  • If the first expr 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 first expr is not numeric, then each expr after the first is implicitly converted to the data type of the first expr before the comparison.

Example

Here’s an example:

SELECT GREATEST('a', 'b', 'c')
FROM DUAL;

Result:

c

Here are some more:

SELECT 
    GREATEST('A', 'a') AS "r1",
    GREATEST('Cat', 'Dog', 'Dot') AS "r2",
    GREATEST(1, 2, 3) AS "r3",
    GREATEST(1, '2', 3) AS "r4",
    GREATEST('Cat', '2001-12-31') AS "r5"
FROM DUAL;

Result:

   r1     r2    r3    r4     r5 
_____ ______ _____ _____ ______ 
a     Dot        3     3 Cat

Expressions

The arguments can include expressions like this:

SELECT GREATEST(2 * 3, 1 * 3)
FROM DUAL;

Result:

6

Dates

Here’s a comparison of date strings:

SELECT GREATEST(DATE '2020-01-01', DATE '2021-01-01')
FROM DUAL;

Result:

01-JAN-21

The date is returned in the date format of the current session. See How to Check the Date Format of the Current Session.

Null Values

If any argument is null, the result is null:

SET NULL 'null';
SELECT 
    GREATEST(null, 2),
    GREATEST(1, null)
FROM DUAL;

Result:

   GREATEST(NULL,2)    GREATEST(1,NULL) 
___________________ ___________________ 
null                               null

By default, SQLcl and SQL*Plus return a blank space whenever a null value occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Invalid Argument Count

Calling GREATEST() without any arguments results in an error:

SELECT GREATEST()
FROM DUAL;

Result:

SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"

You can also use LEAST() to return the least of a list of one or more expressions.