min(max+max) on 2 tables

[kris123]

Hi Friends,

I have two tables EMP1 and EMP2 with column hiredate.
I want to get the mininum of the max(hiredate) of the two tables.
Can u help me pls....

This doesnt work:
A.
SELECT MIN(SELECT MAX(HIREDATE) FROM EMP1 UNION
SELECT MAX(HIREDATE) FROM EMP2)
FROM DUAL;

B.
SELECT MIN(HIREDATE) FROM
(SELECT MAX(HIREDATE) FROM EMP1 UNION
SELECT MAX(HIREDATE) FROM EMP2);

Any other constructs pls
Thanks a lot

[tamilselvan]

===
SELECT MIN(HIREDATE) FROM
(SELECT MAX(HIREDATE) FROM EMP1 UNION
SELECT MAX(HIREDATE) FROM EMP2);

==

You don't need UNION. Just use UNION ALL.

SELECT MIN(HIREDATE) 
FROM  (SELECT MAX(HIREDATE) FROM EMP1 
       UNION ALL
       SELECT MAX(HIREDATE) FROM EMP2
      );

If there are indexes on hiredate col, then they may be used.

Or
select min ( (select max(hiredate) from emp1, 
             (select max(hiredate) from emp2)
            )
from dual;

Here, the index will not be used. 

Tamil

[gandolf989]

How about this?

SELECT MIN(hiredate) hiredate
  FROM (
 ( SELECT MAX(hiredate) hiredate
     FROM emp1 ) emp1
    UNION ALL
 ( SELECT MAX(hiredate) 
     FROM emp2 ) emp2 );

[kris123]

thanks falks