select t1.a, 
         (select t2.b from t2
          where t2.b = t1.b
          order by t2.c) as fld1
from t1
where rownum = 1

  1  select
  2   dname
  3  ,(select max(ename) KEEP (DENSE_RANK LAST ORDER BY sal)
  4    from emp e
  5    where e.deptno=d.deptno
  6    --order by sal
  7   )  t
  8* from dept d
SQL> /

DNAME          T
-------------- ----------
ACCOUNTING     KING
RESEARCH       SCOTT
SALES          BLAKE
OPERATIONS