ORACLE MAX FUNCTION

ORACLE MAX FUNCTION

                                 max function finds max value in the column, ignores null values.

SYNTAX: MAX(expr)

MAXIMUM SALARY IN THE COMPNAY.

----------------------------------------------------

SQL> select max(salary) from employees;

MAX(SALARY)
-----------
      24000

Get MAXIMUM SALARY DRAWN FROM EACH DEPT.

------------------------------------------------------------------------

SQL> select department_ID,max(Salary) from employees
  2  group by department_ID;

DEPARTMENT_ID MAX(SALARY)
------------- -----------
          100       12008
           30       11000
                     7000
           90       24000
           20       13000
           70       10000
          110       12008
           50       11808
           80       14000
           40        6500
           60        9000
           10        4400

12 rows selected.

Note: Group by department and then find max(salary)

GET MAX SALARY DRAWN ON EACH JOB CATEGORY

---------------------------------------------------------------------------

SQL> select job_ID,MAX(SALARY) from employees
  2  group by job_id;

JOB_ID     MAX(SALARY)
---------- -----------
IT_PROG           9000
AC_MGR           12008
AC_ACCOUNT        8300
ST_MAN           11808
PU_MAN           11000
AD_ASST           4400
AD_VP            17000
SH_CLERK          4200
FI_ACCOUNT        9000
FI_MGR           12008
PU_CLERK          3100
SA_MAN           14000
MK_MAN           13000
PR_REP           10000
AD_PRES          24000
SA_REP           11500
MK_REP            6000
ST_CLERK          5184
HR_REP            6500

19 rows selected.

Note: Group by Job category and then find max(salary)

GET SECOND HIGHEST SALARY  

---------------------------------------------------

SQL> select max(salary) from employees where salary < (select Max(salary) from employees);

MAX(SALARY)
-----------
      17000

Note: First find MAX salary then in remaining values find MAX salary.