ТЕМА:

WITH DEPTS AS (SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL)


SELECT DEPARTMENT_ID, lAST_NAME, SALARY
FROM (SELECT DEPARTMENT_ID, LAST_NAME, SALARY 
FROM EMPLOYEES) OUTER
WHERE DEPARTMENT_ID = (SELECT * 
FROM DEPTS
WHERE DEPARTMENT_ID = OUTER.DEPARTMENT_ID)
ORDER BY DEPARTMENT_ID, SALARY DESC;

select  deptno,
        ename,
        sal,
        rank() over(partition by deptno order by sal desc) salary_rank_analytic,
        (select nvl(count(*),0) + 1 from emp e2 where e2.deptno = e1.deptno and e2.sal > e1.sal) salary_rank_subquery
  from  emp e1
  order by deptno,
           salary_rank_subquery
/

    DEPTNO ENAME             SAL SALARY_RANK_ANALYTIC SALARY_RANK_SUBQUERY
---------- ---------- ---------- -------------------- --------------------
        10 KING             5000                    1                    1
        10 CLARK            2450                    2                    2
        10 MILLER           1300                    3                    3
        20 SCOTT            3000                    1                    1
        20 FORD             3000                    1                    1
        20 JONES            2975                    3                    3
        20 SMITH             800                    4                    4
        20 ADAMS             800                    4                    4
        30 ALLEN            1500                    1                    1
        30 BLAKE            1500                    1                    1
        30 TURNER           1500                    1                    1

    DEPTNO ENAME             SAL SALARY_RANK_ANALYTIC SALARY_RANK_SUBQUERY
---------- ---------- ---------- -------------------- --------------------
        30 JAMES            1250                    4                    4
        30 MARTIN           1250                    4                    4
        30 WARD             1250                    4                    4

14 rows selected.

SQL>