orclz>
orclz> SELECT EMP_ID,EMP_NAME,SALARY,RNK FROM
  2  (SELECT EMP_ID,EMP_NAME,SALARY,
  3  RANK()OVER (PARTITION BY EMP_DEP_ID ORDER BY SALARY DESC) RNK
  4  FROM EMP)
  5  WHERE RNK IN (1,2);
RANK()OVER (PARTITION BY EMP_DEP_ID ORDER BY SALARY DESC) RNK
                                             *
ERROR at line 3:
ORA-00904: "SALARY": invalid identifier


orclz>

orclz> ed
Wrote file afiedt.buf

  1  SELECT EMPNO,ENAME,SAL,RNK,DEPTNO FROM
  2  (SELECT EMPNO,ENAME,SAL,
  3  RANK()OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RNK,DEPTNO
  4  FROM EMP)
  5* WHERE RNK IN (1,2)
orclz> /

      7839 KING             5000          1         10
      7782 CLARK            2450          2         10
      7788 SCOTT            3000          1         20
      7902 FORD             3000          1         20
      7698 BLAKE            2850          1         30
      7499 ALLEN            1600          2         30
orclz>

SELECT EMPNO,
  ENAME,
  DEPTNO,
  SAL,
  RNK
FROM
  (SELECT EMPNO,
    ENAME,
    DEPTNO,
    SAL,
    RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ) RNK
  FROM EMP
  )
WHERE RNK IN (1,2);

DECLARE
  CURSOR C1
  IS
    SELECT DISTINCT DEPTNO FROM EMP;
  CURSOR C2(LV_DEPTNO NUMBER)
  IS
    SELECT ROWNUM RNK,
      EMPNO,
      ENAME,
      DEPTNO,
      SAL
    FROM
      (SELECT EMPNO,
        ENAME,
        DEPTNO,
        SAL
      FROM EMP
      WHERE DEPTNO=LV_DEPTNO
      ORDER BY SAL DESC
      )
  WHERE ROWNUM<3 ;
BEGIN
  FOR I IN C1
  LOOP
    FOR J IN C2(I.DEPTNO)
    LOOP
      DBMS_OUTPUT.PUT_LINE(J.EMPNO||' | '||J.ENAME||' | '||I.DEPTNO||' | '||J.RNK||' | '||J.SAL);
    END LOOP;
  END LOOP;
END;