[Oracle] Order by절

doc 문서.

https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj13658.html

https://oracle-base.com/articles/misc/sql-for-beginners-the-order-by-clause

샘플데이터 생성.

// departments 테이블 생성
CREATE TABLE departments (
  department_id   NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
  department_name VARCHAR2(14),
  location        VARCHAR2(13)
);

// departments 데이터 생성
INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;

// employees 테이블 생성
CREATE TABLE employees (
  employee_id   NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
  employee_name VARCHAR2(10),
  job           VARCHAR2(9),
  manager_id    NUMBER(4),
  hiredate      DATE,
  salary        NUMBER(7,2),
  commission    NUMBER(7,2),
  department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);

// employees 데이터 생성
INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-2-87','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-2-87', 'dd-mm-yyyy'),1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

Order by.

- 테이블에서 데이터를 조회 시 정렬 명령어

- SelectExpression에서 사용

SelectExporession - SELECT-FROM-WHERE 

- Subquery

Subquery - 하나의 SQL문안에 작성하는 내부의 SELECT 쿼리

Syntax.

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

샘플 데이터.

department_id	department_name	location
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

부서ID(department id)로 오름차순.

* ASC는 생략 가능 - Default

SELECT * 
  FROM departments 
 ORDER BY DEPARTMENT_ID ASC

결과.(아래)

department_id	department_name	location
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

부서ID(department id)로 내림차순.

SELECT * 
  FROM departments 
 ORDER BY DEPARTMENT_ID DESC

결과.(아래)

department_id	department_name	location
40	OPERATIONS	BOSTON
30	SALES	CHICAGO
20	RESEARCH	DALLAS
10	ACCOUNTING	NEW YORK

컬럼번호로 정렬.

내 경험상 거의 사용하진 않았다.(아래)

SELECT * 
  FROM departments 
 ORDER BY 1 DESC

Alias를 이용하여 정렬.

* Alias: 별칭으로 사용하며 컬럼간 연산을 통해 명칭이 길어질 경우, 컬럼명을 변경해야될 경우 사용함

SELECT department_id AS DEPT_ID
     , department_name AS DEPT_NM
     , location AS LOC
  FROM departments 
 ORDER BY DEPT_ID DESC

결과.(아래)

DEPT_ID	DEPT_NM	LOC
40	OPERATIONS	BOSTON
30	SALES	CHICAGO
20	RESEARCH	DALLAS
10	ACCOUNTING	NEW YORK