drop table t1;
drop table t2;
create table t1 as select * from scott.emp;
create table t2 as select * from scott.emp;
update t2 set sal=sal+1000;
commit;
--sql
select t1.*,(select max(sal) from t2 where t2.deptno = t1.deptno and t2.sal>2900)asmax
from t1order bymax , empno;
