[SQLD : Ⅲ. SQL 기본] 8. ORDER BY 절

* ORDER BY 정렬

 SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력하는데 사용

 칼럼(Column)명 대신에 SELECT 절에서 사용한 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용 가능

 별도로 정렬 방식을 지정하지 않으면 기본적으로 오름차순이 적용

SELECT 칼럼명 [ALIAS명] 
FROM 테이블명 
[WHERE 조건식] 
[GROUP BY 칼럼(Column)이나 표현식] 
[HAVING 그룹조건식] 
[ORDER BY 칼럼(Column)이나 표현식 [ASC 또는 DESC]] ;
ASC(Ascending) : 조회한 데이터를 오름차순으로 정렬한다.(기본 값이므로 생략 가능) 
DESC(Descending) : 조회한 데이터를 내림차순으로 정렬한다.

[예제] ORDER BY 절의 예로 선수 테이블에서 선수들의 이름, 포지션, 백넘버를 출력하는데

사람 이름을 내림차순으로 정렬하여 출력한다.

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버 
FROM PLAYER 
ORDER BY PLAYER_NAME DESC;

[예제] ORDER BY 절의 예로 선수 테이블에서 선수들의 이름, 포지션, 백넘버를 출력하는데

선수들의 포지션 내림차순으로 출력한다. 칼럼명이 아닌 ALIAS를 이용한다.

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버 
FROM PLAYER 
ORDER BY 포지션 DESC;

 ORDER BY 절 특징

- 기본적인 정렬 순서는 오름차순(ASC)이다.

- 숫자형 데이터 타입은 오름차순으로 정렬했을 경우에 가장 작은 값부터 출력된다.

- 날짜형 데이터 타입은 오름차순으로 정렬했을 경우 날짜 값이 가장 빠른 값이 먼저 출력된다.

  예를 들어 ‘01-JAN-2012’는 ‘01-SEP-2012’보다 먼저 출력된다.

- Oracle에서는 NULL 값을 가장 큰 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 마지막에,

  내림차순으로 정렬했을 경우에는 가장 먼저 위치한다.

- 반면, SQL Server에서는 NULL 값을 가장 작은 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 먼저,

  내림차순으로 정렬했을 경우에는 가장 마지막에 위치한다.

[예제] 한 개의 칼럼이 아닌 여러 가지 칼럼(Column)을 기준으로 정렬해본다. 먼저 키가 큰 순서대로, 키가 같은 경우 백넘버 순으로 ORDER BY 절을 적용하여 SQL 문장을 작성하는데, 키가 NULL인 데이터는 제외한다.

SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
FROM PLAYER 
WHERE HEIGHT IS NOT NULL 
ORDER BY HEIGHT DESC, BACK_NO;

[예제] ORDER BY 절의 예로 선수 테이블에서 선수들의 이름, 포지션, 백넘버를 출력하는데

선수들의 백넘버 내림차순, 백넘버가 같은 경우 포지션, 포지션까지 같은 경우 선수명 순서로 출력한다.

BACK_NO가 NULL인 경우는 제외하고, 칼럼명이나 ALIAS가 아닌 칼럼 순서를 매핑하여 사용한다.

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버
FROM PLAYER
WHERE BACK_NO IS NOT NULL 
ORDER BY 3 DESC, 2, 1;

[예제] DEPT 테이블 정보를 부서명, 지역, 부서번호 내림차순으로 정렬해서 출력한다.

아래의 SQL 문장은 출력되는 칼럼 레이블은 다를 수 있지만 결과는 모두 같다.

Case1. 칼럼명 사용 ORDER BY 절 사용
SELECT DNAME, LOC, DEPTNO 
FROM DEPT 
ORDER BY DNAME, LOC, DEPTNO DESC;

Case2. 칼럼명 + ALIAS 명 사용 ORDER BY 절 사용
SELECT DNAME DEPT, LOC AREA, DEPTNO 
FROM DEPT 
ORDER BY DNAME, AREA, DEPTNO DESC;

Case3. 칼럼 순서번호 + ALIAS 명 사용 ORDER BY 절 사용
SELECT DNAME, LOC AREA, DEPTNO 
FROM DEPT 
ORDER BY 1, AREA, 3 DESC;

 

* SELECT 문장 실행 순서

 1. 발췌 대상 테이블을 참조한다. (FROM)

 2. 발췌 대상 데이터가 아닌 것은 제거한다. (WHERE)

 3. 행들을 소그룹화 한다. (GROUP BY)

 4. 그룹핑된 값의 조건에 맞는 것만을 출력한다. (HAVING)

 5. 데이터 값을 출력/계산한다. (SELECT)

 6. 데이터를 정렬한다. (ORDER BY)

옵티마이저가 SQL 문장의 SYNTAX, SEMANTIC 에러를 점검하는 순서이기도 하다.

[예제] SELECT 절에 없는 EMP 칼럼을 ORDER BY 절에 사용한다.

SELECT EMPNO, ENAME 
FROM EMP 
ORDER BY MGR;
 ORDER BY 절에서 SELECT 절에서 정의하지 않은 칼럼을 사용해도 문제없음

[예제] 인라인 뷰에 정의된 SELECT 칼럼을 메인쿼리에서 사용한다.

SELECT EMPNO 
FROM (SELECT EMPNO, ENAME 
      FROM EMP 
      ORDER BY MGR); 
14개의 행이 선택되었다.
 SELECT 절에서 정의한 칼럼은 메인쿼리에서도 사용할 수 있는 것을 확인

[예제] 인라인 뷰에 미정의된 칼럼을 메인쿼리에서 사용해본다.

SELECT MGR 
FROM (SELECT EMPNO, ENAME 
      FROM EMP
      ORDER BY MGR); 
SELECT MGR 
FROM ;
* ERROR: "MGR": 부적합한 식별자
서브쿼리의 SELECT 절에서 선택되지 않은 칼럼들은 계속 유지되는 것이 아니라 
서브쿼리 범위를 벗어나면 더 이상 사용할 수 없게 된다.

 SELECT 절에서는 그룹핑 기준과 숫자 형식 칼럼의 집계 함수를 사용할 수 있지만,

 그룹핑 기준 외의 문자 형식 칼럼은 정할 수 없다.

[예제] GROUP BY 절 사용시 SELECT 절에 일반 칼럼을 사용해본다.

 SELECT JOB, SAL 
 FROM EMP 
 GROUP BY JOB 
 HAVING COUNT(*) > 0 
 ORDER BY SAL; 
 SELECT JOB, SAL ; * ERROR: GROUP BY 표현식이 아니다.

[예제] GROUP BY 절 사용시 ORDER BY 절에 일반 칼럼을 사용해본다.

SELECT JOB 
FROM EMP 
GROUP BY JOB 
HAVING COUNT(*) > 0 
ORDER BY SAL; 
ORDER BY SAL; * ERROR: GROUP BY 표현식이 아니다.

[예제] GROUP BY 절 사용시 ORDER BY 절에 집계 칼럼을 사용해본다.

SELECT JOB 
FROM EMP
GROUP BY JOB 
HAVING COUNT(*) > 0 
ORDER BY MAX(EMPNO), MAX(MGR), SUM(SAL), COUNT(DEPTNO), MAX(HIREDATE);

SELECT SQL에서 GROUP BY 절이 사용되었기 때문에

SELECT 절에 정의하지 않은 MAX, SUM, COUNT 집계 함수도 ORDER BY 절에서 사용할 수 있음

 

* TOP N 쿼리

 - ROWNUM

  순위가 높은 N개의 로우를 추출하기 위해 ORDER BY 절과 WHERE 절의 ROWNUM 조건을 같이 사용

   정렬이 완료된 후 데이터의 일부가 출력되는 것이 아니라,

   데이터의 일부가 먼저 추출된 후(ORDER BY 절은 결과 집합을 결정하는데 관여하지 않음)

   데이터에 대한 정렬 작업이 일어나므로 주의

[예제] 사원 테이블에서 급여가 높은 3명만 내림차순으로 출력하고자 하는데, 잘못 사용된 SQL의 사례이다.

SELECT ENAME, SAL 
FROM EMP 
WHERE ROWNUM < 4 
ORDER BY SAL DESC;
급여 순서에 상관없이 무작위로 추출된 3명에 한해서 
급여를 내림차순으로 정렬한 결과이므로 원하는 결과를 출력한 것이 아니다.

[예제] ORDER BY 절이 없으면 ORACLE의 ROWNUM 조건과 SQL SERVER의 TOP 절은 같은 결과를 보인다.

ORDER BY 절이 사용되는 경우 ORACLE은 ROWNUM 조건을 ORDER BY 절보다 먼저 처리되는 WHERE 절에서 처리하므로, 정렬 후 원하는 데이터를 얻기 위해서는 2장 4절에서 배울 인라인 뷰에서 먼저 데이터 정렬을 수행한 후 메인쿼리에서 ROWNUM 조건을 사용해야 한다.

SELECT ENAME, SAL 
FROM (SELECT ENAME, SAL 
      FROM EMP 
      ORDER BY SAL DESC)
WHERE ROWNUM < 4 ;

인라인 뷰를 사용하여 추출하고자 하는 접합을 정렬한 후 ROWNUM을 적용시킴으로써

결과에 참여하는 순서와 추출되는 로우 순서를 일치시킴으로써 Top N 쿼리의 결과를 만듦

 

 - TOP()

   SQL Server는 TOP 조건을 사용하게 되면 별도 처리 없이 관련 Order By 절의 데이터 정렬 후

   원하는 일부 데이터만 쉽게 출력 가능

TOP (Expression) [PERCENT] [WITH TIES]

 WITH TIES 옵션은 ORDER BY 절의 조건 기준으로 TOP N의 마지막 행으로 표시되는 추가 행의 데이터가 같을 경우

 N+ 동일 정렬 순서 데이터를 추가 반환하도록 지정하는 옵션

[예제] 사원 테이블에서 급여가 높은 2명을 내림차순으로 출력하고자 한다.

SELECT TOP(2) ENAME, SAL 
FROM EMP 
ORDER BY SAL DESC;

[예제] 사원 테이블에서 급여가 높은 2명을 내림차순으로 출력하는데 같은 급여를 받는 사원이 있으면 같이 출력한다.

SELECT TOP(2) WITH TIES ENAME, SAL 
FROM EMP 
ORDER BY SAL DESC;

TOP(2) WITH TIES 옵션은 동일 수치의 데이터를 추가로 더 추출하는 것으로,

SCOTT과 FORD의 급여가 공동 2위이므로 TOP(2) WITH TIES의 실행 결과는 3건의 데이터가 출력된다.