MIN and MAX Analytic Functions in Oracle SQL
MIN
MIN function is used to fetch the minimum value from the set of data. you can also separated by group by clause.
MAX
MAX function is used to fetch the maximum value from the set of data. you can also separated by group by clause.
Examples of Min and Max function:
Fetch the Minmum and maximum salary from the employee table.
select min(salary),max(salary) from employees;
MIN(SALARY) MAX(SALARY)
----------- -----------
2100 24000
Use group by clause for finding department wise min and max salary
select department_id,min(salary),max(salary) from employees
group by department_id order by 1;
DEPARTMENT_ID MIN(SALARY) MAX(SALARY)
------------- ----------- -----------
10 4400 4400
20 6000 13000
30 2500 11000
40 6500 6500
50 2100 8200
Use OVER clause to find minimum and maximum salary department wise.
SELECT employee_id,
department_id,
salary,
MIN(salary) OVER (PARTITION BY department_id) AS MIN_SALARY_BY_DEPT,
MAX(salary) OVER (PARTITION BY department_id) AS MAX_SALARY_BY_DEPT
FROM employees order by department_id,salary;
EMPLOYEE_ID DEPARTMENT_ID SALARY MIN_SALARY_BY_DEPT MAX_SALARY_BY_DEPT
----------- ------------- ---------- ------------------ ------------------
200 10 4400 4400 4400
202 20 6000 6000 13000
201 20 13000 6000 13000
119 30 2500 2500 11000
118 30 2600 2500 11000
117 30 2800 2500 11000
116 30 2900 2500 11000
115 30 3100 2500 11000
114 30 11000 2500 11000
203 40 6500 6500 6500
132 50 2100 2100 8200