Greatest function used to find the maximum value within the numbers specified.
Least function used to find the number value within the numbers specified.
Both are single-row mathematical functions.
Examples:-
Greatest
SQL> select greatest(-1,0,1,2,3) from dual;
GREATEST(-1,0,1,2,3)
——————–
3
SQL> select greatest(-1,0,1,2,null) from dual;
GREATEST(-1,0,1,2,NULL)
———————–
NULL
Caution :- If any of the argument is null then Greatest function returns null
SQL> select greatest(-1,0,1,2,’a’) from dual;
select greatest(-1,0,1,2,’a’) from dual
*
ERROR at line 1:
ORA-01722: invalid number
Note :- Every argument must be a number datatype.
Least
SQL> select least(0,1,2,3) from dual;
LEAST(0,1,2,3)
————–
0
SQL> select least(-1,0,1,2,3) from dual;m
LEAST(-1,0,1,2,3)
—————–
-1
SQL> select least(-1,0,1,2,’a’) from dual;
select least(-1,0,1,2,’a’) from dual
*
ERROR at line 1:
ORA-01722: invalid number
Note :- Every argument must be a number datatype.
SQL> select least(-1,0,1,2,null) from dual;
LEAST(-1,0,1,2,NULL)
———————–
NULL
Caution :- If any of the argument is null then Least function returns null
Consider below table
SQL> desc salary;
Name Null? Type
—————————————– ——– ——
SAL_NO NUMBER
SAL NUMBER
COMM NUMBER
SQL> select * from salary;
SAL_NO SAL COMM
———- ———- ———-
1 1000 800
2 1000 1200
3 1300 1300
4 1500 600
SQL> select s.*,greatest(sal,comm) high,least(sal,comm) low from salary s;
SAL_NO SAL COMM HIGH LOW
———- ———- ———- ———- ———-
1 1000 800 1000 800
2 1000 1200 1200 1000
3 1300 1300 1300 1300
4 1500 600 1500 600
Greatest and Least applied to each row
To avoid null problem re-write above query into
SQL> select s.*,greatest(nvl(sal,0),nvl(comm,0)) high,least(nvl(sal,0),nvl(comm,0)) low from salary s;
Oracle PL/SQL Technology blog 
as we know we use max() and min() to get the highest and lowest value vertically columnwise
but we use greatest() and least() to get the max and min value horizontally from a row of the table
“ORA-01722: invalid number” is caused by a type mismatch. Least and greatest can work on string values.
For example:
select least(‘Apple’,’Berry’,’Chestnut’) from dual;
returns ‘Apple’.
This is done based on the character number code.
Because in your example, the first argument is a numeric value, it expects all arguments to be numeric values. If the first argument would have been text, the rest could be numeric and it will automatically be converted:
select greatest(‘a’,-1,0,1,2) from dual;
will return ‘a’ without error.