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;
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.