Find mix and max in sql from each partition

Question

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)

Answer 1

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);