How to list the employees who are senior to most recently hired employee working under a particular manager in the below schema?

0
votes

I have a schema of emp table defined as emp ( empno ,ename ,job ,mgr ,hiredate) and I have to evaluate the query for the problem statement :

List the employees who are senior to most recently hired employee working under king.

I wrote the query as :

select hiredate from emp where hiredate < ANY 
( select e.hiredate from emp e where e.mgr= (select e.empno from emp e 
where e.ename='KING') order by hiredate desc )

This query is giving syntax error ,please help me out .

4
sqloracle
Why the ORDER BY in the sub-query? Don't use same table alias several times in a query. – jarlh
Order by used is to get the most recently hired employee whose manager is kING – Radha Gogia
Tag RDBMS which you are using? – K.K
But you still select all rows. That ORDER BY makes no difference at all. – jarlh
I have edited my query as : select hiredate from emp where hiredate < (select max(hiredate) from emp e where e.mgr=(select e.empno from emp e where e.ename='KING')) ,It is working fine but then why is my query mentioned above in the question – Radha Gogia

4 Answers

1
votes

Use max function instead-

select hiredate 
from emp 
where hiredate < ( select max(hiredate) 
                   from emp 
                   where mgr= (select e.empno 
                               from emp e 
                                where e.ename='KING'));
0
votes

To get employees working under King who joined in the last 7 days, you can try:

SELECT e.*
FROM emp e
WHERE e.mgr = (SELECT empno FROM emp WHERE ename='KING') AND
      e.hiredate > DATEADD(day, -7, GETDATE())

Note that the subquery to find King's employee number is not correlated. So this is the friendly sort of subquery.

0
votes

Use a max to find the greatest hire date, and a join instead of the subquery.

select * from emp where hiredate < 
  (select max(e.hiredate) 
   from emp e 
   join emp mg on e.mgr = mg.empno
   where mg.ename = 'KING')

I guess you have this error message

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

So you should add a TOP 1 to have it work (but well, is this really more readable ?)

select hiredate from emp where hiredate < ANY 
(select TOP 1 e.hiredate from emp e where e.mgr= (select e.empno from emp e 
where e.ename='KING') order by hiredate desc )
0
votes

SELECT * FROM EMP WHERE HIREDATE < ANY (SELECT HIREDATE FROM EMP WHERE ENAME = 'MILLER');