SELECT e.ename, e.job, e.hiredate, e.sal, r.max_sal 
FROM scott.emp e, LATERAL(SELECT a.empno,  
                                 RANK() OVER(ORDER BY a.sal DESC) rk, 
                                 MAX(a.sal) OVER() max_sal 
                          FROM scott.emp a 
                          WHERE a.hiredate<=e.hiredate) r 
WHERE e.empno=r.empno  
  AND rk=2 
ORDER BY e.hiredate

WITH x AS ( 
SELECT ename, job, hiredate, sal, MAX(sal)OVER(ORDER BY hiredate) max_sal 
FROM scott.emp a 
) 
SELECT * 
FROM x 
WHERE 1=(SELECT COUNT(*) 
         FROM scott.emp 
         WHERE hiredate<=x.hiredate 
           AND sal>x.sal) 
ORDER BY hiredate

WITH x AS ( 
SELECT ename, job, hiredate, sal, MAX(sal)OVER(ORDER BY hiredate) max_sal, 
       (SELECT COUNT(*)+1 
        FROM scott.emp 
        WHERE sal>e.sal  
          AND hiredate<=e.hiredate) rk 
FROM scott.emp e 
) 
SELECT ename, job, hiredate, sal, max_sal 
FROM x 
WHERE rk=2 
ORDER BY hiredate

SELECT a.ename, a.job, a.hiredate, a.sal, MAX(b.sal) max_sal 
FROM scott.emp a JOIN scott.emp b ON b.hiredate<=a.hiredate 
                                 AND b.sal>a.sal 
GROUP BY a.ename, a.job, a.hiredate, a.sal 
HAVING COUNT(DISTINCT b.empno)=1 
ORDER BY a.hiredate

WITH x AS ( 
SELECT ename, job, hiredate, sal, 
       MAX(sal) OVER(ORDER BY hiredate) max_sal 
FROM scott.emp  
), y  AS ( 
SELECT ename, job, hiredate, sal, max_sal, MAX(sal) OVER(ORDER BY hiredate) max_sal2 
FROM x 
WHERE sal<max_sal 
) 
SELECT ename, job, hiredate, sal, max_sal 
FROM y 
WHERE sal=max_sal2 
ORDER BY hiredate

WITH e AS (
SELECT ename, job, sal, hiredate
FROM scott.emp
ORDER BY hiredate
), x AS (
SELECT ename, job, sal, hiredate, ROWNUM rn
FROM e
), y(max_sal, sal2, rn) AS (
SELECT sal, 0, 1
FROM x
WHERE rn=1
UNION ALL
SELECT GREATEST(x.sal, y.max_sal) AS max_sal,
       CASE WHEN x.sal>y.max_sal THEN y.max_sal
            WHEN x.sal>y.sal2 AND x.sal<=y.max_sal THEN x.sal 
            ELSE y.sal2 
       END AS sal2,
       x.rn
FROM x JOIN y ON x.rn=y.rn+1
)
SELECT x.ename, x.job, x.sal, x.hiredate, y.max_sal
FROM y JOIN x ON y.rn=x.rn AND y.sal2=x.sal;

WITH x AS (
SELECT *
FROM scott.emp
MODEL
DIMENSION BY (ROW_NUMBER() OVER(ORDER BY hiredate) rn)
MEASURES(ename, job, sal, hiredate, sal max_sal, 0 sal2)
RULES(
    max_sal[rn>1] = GREATEST(max_sal[CV()-1], sal[CV()]),
    sal2[rn>1]    = CASE WHEN sal[CV()]> max_sal[CV()-1] THEN max_sal[CV()-1]
                         WHEN sal[CV()]> sal2[CV()-1]  
				     	  AND sal[CV()]<=max_sal[CV()-1] THEN sal[CV()] 
                         ELSE sal2[CV()-1]
                    END
     )
)
SELECT ename, job, sal, hiredate, max_sal
FROM x
WHERE sal=sal2;