PLSQL | GREATEST Function

The GREATEST is an inbuilt function in PLSQL which is used to return the greatest value from a given list of some expressions. These expressions may be numbers, alphabets etc.

Syntax:

GREATEST(exp1, exp2, ... exp_n)

Parameters Used:
This function accept some parameters like exp1, exp2, … exp_n. These each expression may be numbers or alphabets on which GREATEST() function is called.

Return Value:
This function returns the greatest value from a given list of expressions.

Supported Versions of Oracle/PLSQL is given below:

1. Oracle 12c
2. Oracle 11g
3. Oracle 10g
4. Oracle 9i
5. Oracle 8i

Let’s see some examples which illustrate the GREATEST function:

Example-1:

DECLARE 
   Test_Number number1 := 1;
   Test_Number number2 := 2;
   Test_Number number3 := 5;
   Test_Number number4 := 30;
   
BEGIN 
   dbms_output.put_line(GREATEST(Test_Number number1, 
                                 Test_Number number2, 
                                 Test_Number number3, 
                                 Test_Number number4)); 
   
END; 

Output:

30

In the above example, some list of numbers is taken as the parameter out of which greatest number is returned as the output. for example, 1, 2, 5 and 30 is taken as the parameter out of which 30 is returned because it is the greatest number.

Example-2:

DECLARE 
   Test_Number number1 := 'a';
   Test_Number number2 := 'b';
   Test_Number number3 := 'c';
   
BEGIN 
   dbms_output.put_line(GREATEST(Test_Number number1, 
                                 Test_Number number2, 
                                 Test_Number number3)); 
   
END;

Output:

c

In the above example, some list of alphabets is taken as the parameter out of which greatest in count alphabet is returned as the output. for example, a, b and c is taken as the parameter out of which c is returned because it is the greatest in the count.

Example-3:

DECLARE 
   Test_Number number1 := 0;
   Test_Number number2 := -4;
   Test_Number number3 := 0.6;
   
BEGIN 
   dbms_output.put_line(GREATEST(Test_Number number1, 
                                 Test_Number number2, 
                                 Test_Number number3)); 
   
END; 

Output:

0.6

In the above example, some list of numbers is taken as the parameter out of which greatest number is returned as the output. for example, 0, -4 and 0.6 is taken as the parameter out of which 0.6 is returned because it is the greatest number.

Advantage:
This function is used to find the greatest expression out of the given input expressions. This expression might be any numbers or alphabets.