グループごとにランクを振る（同率を許し、その分順を飛ばす。例：同率2位が2つあったら、1, 2, 2, 4）

rank_per_group.sql

-- http://otn.oracle.co.jp/otn_pl/otn_tool/code_detail?n_code_id=113

BEGIN;

CREATE TEMPORARY TABLE emp (empno integer, job varchar, ename varchar, sal integer);
INSERT INTO emp (empno, job, ename, sal) VALUES
(9991, 'ANALYST', 'TEST1', 100),
(9992, 'SALESMAN', 'TEST2', 200),
(1, 'ANALYST', 'FORD', 3000),
(2, 'ANALYST', 'SCOTT', 3000),
(3, 'CLERK', 'ADAMS', 1100),
(4, 'CLERK', 'JAMES', 950),
(5, 'CLERK', 'SMITH', 800),
(6, 'MANAGER', 'BLAKE', 2850),
(7, 'MANAGER', 'CLARK', 2450),
(8, 'MANAGER', 'JONES', 2975),
(9, 'PRESIDENT', 'KING', 5000),
(10, 'SALESMAN', 'ALLEN', 1600),
(11, 'SALESMAN', 'MARTIN', 1250),
(12, 'SALESMAN', 'TURNER', 1500),
(13, 'SALESMAN', 'WARD', 1250);

SELECT
    d.job, d.ename, d.sal, c.rank
FROM (SELECT
          a.job, a.sal, COUNT(a.job) AS rank
      FROM
          (SELECT job, sal FROM emp GROUP BY job, sal) a,
          (SELECT job, sal FROM emp GROUP BY job, sal) b
      WHERE
          a.job = b.job
          AND a.sal <= b.sal
      GROUP BY
          a.job, a.sal
      ) c,
      emp d
WHERE
    c.job = d.job
    AND c.sal = d.sal
ORDER BY
    d.job, c.rank;
    
COMMIT;