Greatest and Least function Oracle

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;

←
→

2 responses to “Greatest and Least function Oracle”

  1. debasish sinha Avatar
    debasish sinha

    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

  2. Freekvd Avatar
    Freekvd

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