MIN and MAX Analytic Functions in Oracle SQL

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.