Detailed examples of ORACLE function GREATEST Functions

1 syntax

GREATEST (expr_1, expr_2,... expr_n)

2 Description

The GREATEST (expr_1, expr_2,... expr_n) function returns the maximum number from the expressions (columns, constants, and calculation values) expr_1, expr_2,... expr_n, and so on. During the comparison, ie automatically compares the Data Type of the expression. The data type of expr_1 prevails.

 

3 permitted locations

Procedural and SQL statements.

4. Example 1: Numerical Value]

Expr_1 is Numeric. Compare by size.

All are numeric, and the maximum value is 16:

SQL> SELECT GREATEST (2, 5, 12, 3, 16, 8, 9) A FROM DUAL;

A

----------

16

Some are numeric, but the string can be converted to numeric by implicit Conversion Based on the Data Type of expr_1:

SQL> SELECT GREATEST (2, '5', 12, 3, 16, 8, 9) A FROM DUAL;

A

----------

16

Some of them are numeric type, but if the string cannot be converted to numeric type by implicit type conversion, an error is returned because string A cannot be converted to numeric type:

SQL> SELECT GREATEST (2, 'A', 12, 3, 16, 8, 9) A FROM DUAL;

SELECTGREATEST (2, 'A', 12, 3, 16, 8, 9) A FROM DUAL

 

ORA-01722: Invalid Number

 

4.2 Example 2 [String]

Expr_1 is of limit type. Compare by the first letter (if equal, compare downward)

All are balanced, and the maximum G is retrieved:

SQL> select greatest ('A', 'B', 'C', 'D', 'E', 'F', 'G') A FROM DUAL;

A

-

G

All are character-type and the first letter is equal:

SQL> select greatest ('A', 'B', 'C', 'D', 'E', 'gal', 'gab') A FROM DUAL;

A

---

GAB

Some of them are character-type, which will convert non-linear type into character-type:

SQL> SELECT GREATEST ('A', 6, 7, 5000, 'E', 'F', 'G') A FROM DUAL;

A

-

G

 

4.3 Example 3 [time]

Expr_1 is of the time type.

All are of the time type:

SQL> SELECTGREATEST (sysdate, TO_DATE ('2017-08-01 ', 'yyyy-MM-DD') A FROM DUAL;

A

-----------

 

Some are of the time type and cannot be implicitly converted:

SQL> SELECT GREATEST (sysdate, '2017-08-01 ') A FROMDUAL;

SELECTGREATEST (sysdate, '2017-08-01 ') A FROM DUAL

 

ORA-01861: Text and format strings do not match

 

4.4 Example 4 [null value]

When GREATEST is used to obtain the maximum value, when expr is a function, null values are inevitable. How does the GREATEST function handle null values:

 

When expr_1 is NULL:

SQL> select greatest (NULL, 'B', 'C', 'D', 'E', 'gal', 'gab') A FROM DUAL;

A

-

 

When expr_1 is not NULL and other expr is NULL:

SQL> select greatest ('A', 'B', 'C', 'D', 'E', NULL, 'gab') A FROM DUAL;

A

-

 

It can be found that as long as one of GREATEST's expr is NULL, NULL is returned.