General comparison function uses in oracle database.

There are two types of general comparison function uses in oracle database.

1. GREATEST    Highest value from set of values.
2. LEAST    Lowest value from set of values.

EXAMPLE :-

SQL> select greatest(1,2,3,4) from dual;

GREATEST(1,2,3,4)
-----------------
                4

SQL>
SQL> select least(1,2,3,4) from dual;

LEAST(1,2,3,4)
--------------
             1

SQL> SELECT GREATEST(SALARY,COMMISSION_PCT), LEAST(SALARY,COMMISSION_PCT)
  2  FROM EMPLOYEES
  3  WHERE COMMISSION_PCT IS NOT NULL;

GREATEST(SALARY,COMMISSION_PCT) LEAST(SALARY,COMMISSION_PCT)
------------------------------- ----------------------------
                          14000                           .4
                          13500                           .3
                          12000                           .3
                          11000                           .3
                          10500                           .2
                          10000                           .3
                           9500                          .25
                           9000                          .25
                           8000                           .2
35 rows selected.


SQL> SELECT GREATEST('ABC','AAB','AAA') FROM DUAL;

GRE
---
ABC

SQL> SELECT GREATEST('ABC','ABD','AAA') FROM DUAL;

GRE
---
ABD

SQL> SELECT GREATEST('ABC','ABCD','AAA') FROM DUAL;

GREA
----
ABCD

SQL> SELECT LEAST('ABC','ABCD','AAA') FROM DUAL;

LEA
---
AAA