comparing some values at the table

user_16095037 0
Viewed 19
empno ename job mgr hiredate sal deptno
7839 KING PRESIDENT 2031-11-17 5000.00 10
7934 MILLER CLERK 7782 2032-01-23 1300.00 10
7782 CLARK MANAGER 7839 2031-06-09 2450.00 10
7369 SMITH CLERK 7902 2030-12-17 800.00 20
7876 ADAMS CLERK 7788 2037-05-23 1100.00 20
7566 JONES MANAGER 7839 2031-04-02 2975.00 20
7902 FORD ANALYST 7566 2031-12-03 3000.00 20
7788 SCOTT ANALYST 7566 2037-04-19 2900.00 20
7521 WARD SALESMAN 7698 2031-02-22 1250.00 30
7844 TURNER SALESMAN 7698 2031-09-08 1500.00 30
7900 JAMES CLERK 7698 2031-12-03 950.00 30
7499 ALLEN SALESMAN 7698 2031-02-20 1600.00 30
7698 BLAKE MANAGER 7839 2031-05-01 2850.00 30
7654 MARTIN SALESMAN 7698 2031-09-28 1250.00 30

so i have this table about that have data of the employee. what i want to ask is how to get someone who has bigger sal than the lowest sal manager that has the same deptno with him/her. and the job is not president or manager.

for example at the deptno number 20 the lowest sal manager is supposed to be jones with 2975. the one who has bigger sal than jones and have the same deptno with jones is ford so i want to print this out. i already made my sql and i write it like this

select ename,sal from emp

where sal >= all(select min(sal) from emp where job = 'MANAGER' group by deptno)

and job not like 'PRESIDENT'

and job not like 'MANAGER'

group by deptno, ename,sal;

actually this kind of sql is work pretty well and can print ford out but when i look it again i notice that when i used all it just compare with all the data that took out with the where clause and not compare it by group by deptno number. is there any way to compare the data group by deptno?

compare group-by sql
2 Answers

You need to add a WHERE condition, e1.deptno = e2.deptno, that connects your sub query with the table.

SELECT e1.ename, e1.sal
FROM emp e1
WHERE sal >= (SELECT min(sal) FROM emp e2 WHERE e2.job = 'MANAGER' and e1.deptno = e2.deptno)
AND e1.job not like 'PRESIDENT'
AND e1.job not like 'MANAGER'
GROUP BY e1.deptno, e1.ename, e1.sal;

Kindly accept my answer if it works. Thanks!

user_112893860

Ok here's a way to do this. Notice that slightly modified your query into a join instead of subquery

select e1.*
from emp e1
join 
(select deptno, min(sal) as minsal from emp where job = 'MANAGER' group by deptno) e
on e1.sal>=e.minsal and e1.deptno=e.deptno
AND e1.job not like 'PRESIDENT'
AND e1.job not like 'MANAGER'
user_11232260
Related