Find mix and max in sql from each partition

user_14317371 0
Viewed 113

I am trying to find min and max salaries for an employee along with the dept_id in the below case-

Emp table:

emp_id dept_id salary
----------------------
1         1     100
1         2     200
1         3     300

Desired output:

emp_id dept_id salary
---------------------
1         1     100
1         3     300

This is what I came up with, not sure if this is correct though-

select emp_id, dept_id, salary
from emp x
where salary in (select min(sal) 
                 from emp y 
                 where y.emp_id = x.emp_id)
   or salary in (select max(sal) 
                 from emp y 
                 where y.emp_id = x.emp_id)
max min oracle sql
2 Answers

I think you can do it like this:

select emp_id, dept_id, min(salary) as min_salary, max(salary) as max_salary
from Employees
group by emp_id

This query should do what you're requesting in a cleaner format by using the "group by" clause (this is the Oracle documentation, based on your tag).

Consider learning more about grouping sets.

user_88124610

If you want the rows with the minimum and maximum salaries, then one method uses window funtions:

select emp_id, dept_id, salary
from (select e.*,
             row_number() over (partition by emp_id order by salary asc) as seqnm_asc,
             row_number() over (partition by emp_id order by salary desc) as seqnm_desc
      from emp e
     ) e
where 1 in (seqnum_asc, seqnum_desc);
user_11440350
Related