ORA-01785: order by 항목은 select 목록 식의 수라야 합니다 (ORA-01785: ORDER BY item must be the number of a SELECT-list expression)

ORA-01785: order by 항목은 select 목록 식의 수라야 합니다 오류가 발생하였을 때 해결방법을 알아보도록 합시다. 아울러 오라클에서 Order by 절에 대해 둘러보는 시간입니다~

Oracle Order by 절

Order by는 결과 집합의 순서를 지정하기 위하여 사용합니다. Order By 절은 데이터 조작 언어(DML)를 배울 때 SELECT와 함께 자연스레 배우는 구문이기도 합니다. 이번 시간은 Order by에 대해 익혀보는 시간을 가져보도록 하겠습니다.

구문

Order by 절의 기본적인 구문은 다음과 같습니다. 컬럼 이름과 함께 오름차순, 내림차순을 결정하고 NULLS 값의 우선순위를 지정합니다. 해당 구문은 콤마를 기준으로 반복하여 지정할 수 있습니다.

컬럼 이름 대신 포지션(정수)을 지정할 수 있습니다.
스칼라 서브쿼리, CASE 및 DECODE와 같은 함수도 사용할 수 있습니다.

ORDER BY { columnName }
    [ ASC | DESC ]
    [ NULLS FIRST | NULLS LAST ]
    [ , { column-Name }
        [ ASC | DESC ]
        [ NULLS FIRST | NULLS LAST ]
    ] *

정렬하기 기본

정렬하기는 오름차순, 내림차순 및 NULL값에 대한 순서를 지정할 수 있습니다. 이 부분은 아래에 단계별로 설명해 드리겠습니다. 어떠한 정렬 순서도 지정하지 않은 경우에는 해당 설정의 기본값을 적용합니다. 일반적으로 오름차순과 NULL값의 순서는 후순위로 설정합니다. 예제 테이블 DEPT는 다음의 기본 정렬순서를 가지고 있습니다.

SQL> SELECT * FROM DEPT;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON

정렬을 포지션으로 하기

정렬 순서는 정수로 된 포지션을 지정할 수 있습니다. 포지션은 셀렉트 리스트 내에 있는 컬럼의 개수만큼 1~N까지 지정할 수 있습니다. 컬럼이 많아지는 경우에는 해당 정수가 어떠한 컬럼을 가리키고 있는지 알기가 힘들어지므로 사용 시 주의하셔야 합니다.

SELECT * FROM DEPT ORDER BY 2, 3;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        40 OPERATIONS                   BOSTON
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO

ORA-01785: ORDER BY item must be the number of a SELECT-list expression

예제의 DEPT 테이블은 DEPTNO, DNAME, LOC 3개의 컬럼이 있습니다. 그러므로 1부터 3까지 포지션을 지정할 수 있으며 그 외의 숫자는 ORA-01785: ORDER BY item must be the number of a SELECT-list expression 오류가 발생합니다.

--0으로 지정하는 경우

SELECT * FROM DEPT ORDER BY 0;
ERROR at line 1:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression

--셀렉트 리스트는 총 3개입니다. 4를 지정하는 경우에도 동일한 현상이 발생합니다.

SELECT * FROM DEPT ORDER BY 4;
ERROR at line 1:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression

정렬을 표현식으로 하기

정렬 순서에는 단순히 컬럼, 포지션을 지정하는 것뿐만 아니라 함수를 포함한 표현식을 설정할 수도 있습니다. 다음 예제는 디코드 함수와 함께 DNAME이 'RESEARCH'인 경우 1을 부여하여 우선적으로 조회토록 설정해 보았습니다. (디코드 함수의 인수가 3개인 경우에는 나머지 값은 NULL값입니다. 그 외의 조건은 생략하였으니, 오름차순에서 NULL값은 후순위로 밀려납니다.)

SELECT *
         FROM DEPT
     ORDER BY DECODE (DNAME, 'RESEARCH', 1);

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        20 RESEARCH                     DALLAS
        40 OPERATIONS                   BOSTON
        30 SALES                        CHICAGO
        10 ACCOUNTING                   NEW YORK

오름차순, 내림차순

정렬은 ASC, DESC로 각각 오름차순과 내림차순을 지정할 수 있습니다. 기본값은 ASC이며 생략하는 경우 오름차순으로 설정합니다.

SELECT *
         FROM DEPT
     ORDER BY DEPTNO DESC;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        40 OPERATIONS                   BOSTON
        30 SALES                        CHICAGO
        20 RESEARCH                     DALLAS
        10 ACCOUNTING                   NEW YORK

NULLS FIRST, NULLS LAST

NULL 값과 non-NULL 값 사이의 순서를 설정합니다. 해당 구문을 생략하면 다음과 같이 기본값이 설정됩니다.

오름차순에서 생략되는 경우 : NULL값은 후순위입니다.
내림차순에서 생략되는 경우 : NULL값은 선순위입니다.

컬럼 순서

컬럼 순서는 셀렉트리스트의 앨리어스가 기준입니다. 다음 예제와 같이 퍼스트컬럼, 세컨드컬럼을 기준으로 살펴보도록 합니다. 정렬순서를 퍼스트컬럼으로 하는 경우는 예상과 같이 조회를 합니다.

SQL>   SELECT FIRSTCOLUMN, SECONDCOLUMN
         FROM (SELECT '1' FIRSTCOLUMN, '5' SECONDCOLUMN FROM DUAL
               UNION ALL
               SELECT '2', '4' FROM DUAL
               UNION ALL
               SELECT '3', '3' FROM DUAL
               UNION ALL
               SELECT '4', '2' FROM DUAL
               UNION ALL
               SELECT '5', '1' FROM DUAL)
     ORDER BY FIRSTCOLUMN;

FI SE
-- --
1  5
2  4
3  3
4  2
5  1

그런데 인라인뷰에서 퍼스트컬럼, 세컨드컬럼이었던 것을 조회할 때 퍼스트컬럼은 전혀 다른 앨리어스를 지정하고 세컨드컬럼을 퍼스트컬럼으로 지정하는 경우, 순서는 인라인뷰의 세컨드컬럼이 기준이 됩니다. 셀렉트리스트의 실제 앨리어스이기 때문입니다. 극단적인 예시이지만 실제로 이와 비슷하게 컬럼값을 달리 조회할 때 원하는 순서가 안 나오는 경우에 참고할 수 있을 것입니다.

FIRSTCOLUMN => WHATWASTHEFIRSTCOLUMN
SECONDCOLUMN => FIRSTCOLUMN

SQL>   SELECT FIRSTCOLUMN WHATWASTHEFIRSTCOLUMN, SECONDCOLUMN FIRSTCOLUMN
         FROM (SELECT '1' FIRSTCOLUMN, '5' SECONDCOLUMN FROM DUAL
               UNION ALL
               SELECT '2', '4' FROM DUAL
               UNION ALL
               SELECT '3', '3' FROM DUAL
               UNION ALL
               SELECT '4', '2' FROM DUAL
               UNION ALL
               SELECT '5', '1' FROM DUAL)
     ORDER BY FIRSTCOLUMN;

WH FI
-- --
5  1
4  2
3  3
2  4
1  5