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;