오라클에서는 RANK Function을 사용해서 순위를 간편하게 부여할 수 있습니다.

RANK Function는 oracle 8i(8.1.6) 부터 가능합니다.
8.1.6 이전 버전에서는 사용 할 수 없습니다. ORA-923 error 가 발생 합니다.

plsql 내에서는 oracle 9i 부터 가능합니다. 8.1.6에서는 ORA-900 error가 발생 합니다.

-- scott유저로 접속을 합니다. 
]$ SQLPLUS scott/tiger

-- RANK() 함수를 사용하여 급여 순으로 순위를 부여한 예제 입니다.  
-- RK의 출력값을 보면 급여가 같으면 같은 순위로 부여가 됩니다. 
SQL> SELECT empno, ename, sal, 
        RANK() OVER (ORDER BY sal DESC ) as rk
        FROM emp;

    EMPNO ENAME             SAL         RK
--------- ---------- ---------- ----------
     7839 KING             5000          1
     7788 SCOTT            3000          2
     7902 FORD             3000          2
     7566 JONES            2975          4
     7698 BLAKE            2850          5
     7782 CLARK            2450          6
     7499 ALLEN            1600          7
     7844 TURNER           1500          8
     7934 MILLER           1300          9
     7521 WARD             1250         10
     7654 MARTIN           1250         10
     7876 ADAMS            1100         12
     7900 JAMES             950         13
     7369 SMITH             800         14

그룹별로 순위를 부여 하는 법
위 예제는 deptno를 파티션으로 나누어서 부서별로 순위를 부여 합니다.
특정한 그룹별로 순위를 부여하고 싶을때 사용 하면 편합니다.

SQL> SELECT deptno, ename, sal, 
       RANK() OVER (PARTITION BY deptno ORDER BY sal DESC ) as rk 
       FROM emp ;
 
 DEPTNO ENAME             SAL        RK
------- ---------- ---------- ---------
     10 KING             5000         1
     10 CLARK            2450         2
     10 MILLER           1300         3
     20 SCOTT            3000         1
     20 FORD             3000         1
     20 JONES            2975         3
     20 ADAMS            1100         4
     20 SMITH             800         5
     30 BLAKE            2850         1
     30 ALLEN            1600         2
     30 TURNER           1500         3
     30 WARD             1250         4
     30 MARTIN           1250         4
     30 JAMES             950         6

DENSE_RANK() 함수

DENSE_RANK() – 중복 RANK의 수와 무관하게 numbering을 합니다.
1등, 2등, 2등 이렇게 2등이 중복되었는데 4등이 아니라 3등이 부여 됩니다.

SQL>SELECT empno, ename, sal,  
       DENSE_RANK() OVER (ORDER BY sal DESC ) as rk
       FROM emp;     
    
    EMPNO ENAME             SAL        RK
--------- ---------- ---------- ---------
     7839 KING             5000         1
     7788 SCOTT            3000         2
     7902 FORD             3000         2
     7566 JONES            2975         3
     7698 BLAKE            2850         4
     7782 CLARK            2450         5
     7499 ALLEN            1600         6
     7844 TURNER           1500         7
     7934 MILLER           1300         8
     7521 WARD             1250         9
     7654 MARTIN           1250         9
     7876 ADAMS            1100        10
     7900 JAMES             950        11
     7369 SMITH             800        12  

OVER analytic_clause
해당 함수가 쿼리 결과 집합에 대해 적용되라는 지시어로써 FROM, WHERE, GROUP BY와 HAVING구 이후에 계산되어 진다.
SELECT 구 또는 ORDER BY 구에 Analytic Function을 사용할 수 있다.

– PARTITION BY 구
하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고 하나 이상의 컬럼 또는 표현식에 의한 그룹으로 쿼리의 결과를 파티션한다. 이 구가 생략되면 단일 그룹처럼 쿼리 결과 집합이 처리된다.

– ORDER BY 구
하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고 하나 이상의 컬럼 또는 표현식을 기준으로 파티션 내의 데이터를 정렬한다. 표현식은 컬럼의 별칭 또는 위치를 나타내는 숫자를 사용할 수 없다.

부서별 직업별 급여 합계와, 직업별 급여 합계가 가장 큰 금액

SELECT deptno, job, SUM(SAL) sum_sal,
MAX(SUM(sal)) OVER (PARTITION BY deptno)
FROM emp
GROUP BY deptno, job