Ranking within a group

Like this:

select deptno
     , salary
     , ( select count(*) + 1
           from emp_test
          where deptno = x.deptno
            and salary > x.salary ) as rank
  from emp_test as x
order
    by deptno
     , salary desc 

The subquery is called a scalar subquery because it returns only one value for each row. It's also a correlated subquery, using the correlation variable x. In effect, this is equivalent to grouping, because the count of the number of rows with a higher salary is determined only within each group, the same department.

Any time you're dealing with ranking, you always want to make sure you've covered what happens with ties. Here is some more data to add to yours:

deptno salary 
  10   10000
  10   10000
  10    9370
  20   50000

Now your results should look like this:

deptno salary rank
  10   40000   1
  10   20000   2
  10   10000   3
  10   10000   3
  10   10000   3
  10    9370   6
  20   50000   1
  20   50000   1
  20   30000   3

Please make sure you understand why.