SELECT d.loc,  
       MAX(e1.ename) KEEP(DENSE_RANK FIRST ORDER BY e1.hiredate DESC) ename1,  
       MAX(e1.hiredate) hiredate1,  
       MAX(e2.ename) KEEP(DENSE_RANK FIRST ORDER BY e2.hiredate DESC) ename2,  
       MAX(e2.hiredate) hiredate2  
FROM scott.emp e1 JOIN scott.emp e2 ON e1.deptno=e2.deptno  
                                   AND e1.hiredate>=e2.hiredate  
                                   AND e1.ROWID!=e2.ROWID  
                  JOIN scott.dept d ON e1.deptno=d.deptno  
GROUP BY d.loc

SELECT d.loc,  
       MAX(ename) KEEP(DENSE_RANK FIRST ORDER BY hiredate DESC) ename1,  
       MAX(hiredate) hiredate1,  
       REGEXP_SUBSTR(LISTAGG(ename, '|') WITHIN GROUP (ORDER BY hiredate DESC), '[^|]+',1,2) ename2, 
       REGEXP_SUBSTR(LISTAGG(hiredate, '|') WITHIN GROUP (ORDER BY hiredate DESC), '[^|]+',1,2) hiredate2  
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno  
GROUP BY d.loc  
ORDER BY 1

WITH x AS (  
SELECT d.loc, e.ename, e.hiredate,  
       ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn  
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno  
)  
SELECT a.loc, a.ename ename1, a.hiredate hiredate1,  
              b.ename ename2, b.hiredate hiredate2  
FROM x a JOIN x b ON a.loc=b.loc  
                 AND a.rn=1  
                 AND b.rn=2

WITH x AS ( 
SELECT d.loc, e.ename, e.hiredate,  
       ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn 
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno 
) 
SELECT loc, e1_ename AS ename1, e1_hdate AS hiredate1, 
       e2_ename AS ename2, e2_hdate AS hiredate2 
FROM x 
PIVOT ( 
MAX(ename) ename, MAX(hiredate) hdate FOR rn IN (1 AS e1, 2 AS e2)  
) 
ORDER BY 1

WITH x AS ( 
SELECT d.loc, e.ename, e.hiredate,  
       ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn 
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno 
) 
SELECT loc,  
       MAX(DECODE(rn,1,ename)) ename1,  
       MAX(DECODE(rn,1,hiredate)) hiredate1, 
       MAX(DECODE(rn,2,ename)) ename2,  
       MAX(DECODE(rn,2,hiredate)) hiredate2 
FROM x 
GROUP BY loc 
ORDER BY 1

WITH x AS ( 
SELECT d.loc, e.ename, e.hiredate,  
       ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn 
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno 
) 
SELECT loc, PRIOR ename ename1, PRIOR hiredate hiredate1,  
       ename ename2, hiredate hriedate2 
FROM x 
WHERE rn=2 
START WITH rn=1 
CONNECT BY loc=PRIOR loc 
       AND rn=PRIOR rn+1

WITH x AS ( 
SELECT d.loc, e.ename ename1, e.hiredate hiredate1,  
 LEAD(e.ename,1) OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) ename2, 
 LEAD(e.hiredate,1) OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) hiredate2, 
 ROW_NUMBER()OVER(PARTITION BY d.deptno ORDER BY e.hiredate DESC) rn 
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno 
)  
SELECT loc, ename1, hiredate1, ename2, hiredate2 
FROM x 
WHERE rn=1 
ORDER BY 1

SELECT loc, ename1, hiredate1, ename2, hiredate2 
FROM scott.emp e JOIN scott.dept d ON e.deptno=d.deptno 
MODEL 
RETURN UPDATED ROWS 
PARTITION BY (d.loc) 
DIMENSION BY ( 
   ROW_NUMBER()OVER(PARTITION BY d.loc ORDER BY e.hiredate DESC) AS rn 
) 
MEASURES( 
    ename AS ename1, hiredate AS hiredate1,  
    ename AS ename2, hiredate AS hiredate2 
) 
RULES( 
    ename2[1]   =ename1[2], 
    hiredate2[1]=hiredate1[2] 
) 
ORDER BY 1