Thread: I want deptno and sal who are having highest salary

Hi folks,

I've a scenario my source table having four columns like, ID,NAME,SAL,DEPTNO and my target table having only two columns i'e DEPTNO and SAL. So i want deptno and sal who are having highest salary among all depts. like
ID NAME SAL DEPTNO TARGET
101 XXX 1000 10 DEPTNO SAL
102 YYY 2000 10 10 7000
103 ZZZ 3000 20
104 AAA 7000 10
105 BBB 4000 30

I appriciate you for fast response.

Question asked by visitor ravi kumar

if you want highest salary for each department then you need to run the following query,
select deptno,max(sal) from emp
group by deptno

If you want deptartment id and maximum salary of depts whose maximum salary is maximum irrespective of dept then run the following query,

select distinct deptno,sal from emp
where sal = (select max(sal) from emp)