[오늘쿼리] DENSE_RANK

SELECT ENAME
	  , JOB
      , SAL
      , RANK() OVER (ORDER BY SAL DESC) AS RANK,
      , DENSE_RANK() OVER (ORDER BY SAL DESC) AS DENSE_RANK
FROM EMP
WHERE JOB IN ('ANALYST', 'MANAGER');

출력결과

ENAME | JOB | SAL | RANK | DENSE_RANK
FORD | ANALYST | 3000 | 1 | 1
SCOTT | ANALYST | 3000 | 1 | 1
JONES | MANAGER | 2975 | 3 | 2
BLAKE | MANAGER | 2850 | 5 | 3

• RANK 함수는 순위 1위가 두 명이어서 다음에 바로 3위를 출력
• DENSE_RANK는 2위로 출력

SELECT JOB
      , ENAME
      , SAL
      , DENSE_RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) AS 순위
FROM EMP
WHERE HIREDATE BETWEEN TO_DATE('1981/01/01', 'RRRR/MM/DD')
				   AND TO_DATE('1981/12/31', 'RRRR/MM/DD');

출력결과

JOB | ENAME | SAL | 순위
ANALYST | FORD | 3000 | 1
CLERK | JAMES | 950 | 1
MANAGER | JONSE | 2975 | 1
MANAGER | BLAKE | 2850 | 2
MANAGER | CLARK | 2450 | 3
PRESIDENT | KING | 5000 | 1

SELECT DENSE_RANK(2975) WITHIN GROUP (ORDER BY SAL DESC) 순위
	FROM EMP;

출력결과

순위
3

SELECT DENSE_RANK('81/11/17') WITHIN GROUP (ORDER BY HIREDATE ASC) 순위
	FROM EMP;

출력결과

순위
9