CODEWITHSUNDEEP
sqloracle-database
Radha Gogia
Radha Gogia

Reputation: 795

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

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 .

Upvotes: 0

Views: 5782

Answers (4)

Sameer Basha Shaik
Sameer Basha Shaik

Reputation: 1

SELECT *
FROM   emp
WHERE  hiredate < ANY (SELECT hiredate
                       FROM   emp
                       WHERE  ename = 'MILLER');

Upvotes: 0

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

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 )

Upvotes: 0

gunjan maheshwari
gunjan maheshwari

Reputation: 306

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

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.

Upvotes: 0

Related Questions