ТЕМА:

select  e1.deptno,
        e1.ename,
        e1.sal,
        count(e2.sal) + 1 salary_rank
  from      emp e1
        left join
            emp e2
          on (
                  e2.deptno = e1.deptno
              and
                  e2.sal > e1.sal
             )
  group by e1.deptno,
           e1.ename,
           e1.sal
  order by deptno,
           salary_rank
/

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

    DEPTNO ENAME             SAL SALARY_RANK
---------- ---------- ---------- -----------
        30 WARD             1250           4
        30 JAMES            1250           4
        30 MARTIN           1250           4

14 rows selected.

SQL>