[Oracle] 오라클 서브쿼리 ORDER BY ROWNUM 사용법

오라클의 스칼라 서브쿼리에서 특정 칼럼으로 정렬해서 ROWNUM으로 1건의 데이터를 추출할 때 "ORA-00907: 누락된 우괄호", "ORA-00904: 부적합한 식별자" 오류가 발생할 수 있다.  정렬해야 하는 칼럼과 추출해야 하는 칼럼이 다른 경우 ORDER BY 절과 ROWNUM을 사용하는 방법은 아래의 예시를 참고하면 된다.

 

ORDER BY, ROWNUM 잘못된 예제

위의 쿼리문은 아주 그럴싸하게 작성된 쿼리문으로 보인다. 쿼리문을 실행하면 "ORA-00907: 누락된 우괄호" 오류가 발생한다. 서브쿼리에서는 ORDER BY를 사용할 수 없다.(인라인뷰 서브쿼리는 예외)

 

누락된 우괄호 오류가 발생하지 않더라도 일반적인 쿼리문에서는 ROWNUM이 ORDER BY 절보다 먼저 실행되므로 정렬되지 않은 1건의 데이터가 추출된다. 서브쿼리가 아니라도 일반적인 쿼리문을 작성할 때도 유의해야 한다.

 

목차 서브쿼리에서 정렬 후 1건의 데이터 추출 (오라클 12c 이상) 서브쿼리에서 정렬 후 1건의 데이터 추출 (오라클 11g 이하)

 

서브쿼리에서 정렬 후 1건의 데이터 추출 (오라클 12c 이상)

SELECT a.empno
     , a.ename
     , a.job
     , a.sal
     , (SELECT ename
          FROM (SELECT aa.ename
                  FROM emp aa
                 WHERE aa.job = a.job
                 ORDER BY aa.sal DESC)
         WHERE ROWNUM = 1) AS max_sal_ename
  FROM emp a
 WHERE a.job IN ('MANAGER', 'SALESMAN')

 

 

FROM 절에 사용한 서브쿼리는 인라인 뷰이며, 인라인 뷰에서는 ORDER BY 절을 사용할 수 있다. 인라인 뷰에서 정렬된 데이터를 ROWNUM으로 1건만 추출한 예제이다.

 

서브쿼리에서 직업별 가장 높은 급여의 직원명을 추출한 것을 확인할 수 있다.

 

오라클 11g 이하는 서브쿼리 2DEPTH(인라인 뷰)에서 메인 쿼리문의 테이블 칼럼(a.job)을 참조할 경우 "ORA-00904: 부적합한 식별자" 오류가 발생한다.

 

서브쿼리에서 정렬 후 1건의 데이터 추출 (오라클 11g 이하)

SELECT a.empno
     , a.ename
     , a.job
     , a.sal
     , (SELECT MAX(aa.ename) KEEP(DENSE_RANK FIRST ORDER BY aa.sal DESC)
          FROM emp aa
         WHERE aa.job = a.job) AS max_sal_ename
  FROM emp a
 WHERE a.job IN ('MANAGER', 'SALESMAN')

 

 

서브쿼리를 사용하여 ORDER BY 절과 ROWNUM = 1을 사용한 결과와 동일한 결과가 조회된다.

 

오라클 11g 이하의 버전에서는 2DEPTH의 서브쿼리에서 메인 쿼리문의 테이블 칼럼을 참조하지 못하므로 KEEP 절을 사용하면 동일한 결과를 얻을 수 있다. (오라클 12c 이상에서도 가능)

 

ORDER BY 절의 급여(aa.sal) 칼럼에 동일한 값이 있을 경우 직원명이 MAX인 직원명이 우선 되므로 세밀한 정렬 조건이 필요할 수도 있다.

[Oracle] 오라클 KEEP 사용법 (최저, 최고 순위 값)

[Oracle] 오라클 서브쿼리 종류 및 사용법