ORDER BY 절

-- Oracle: 연습용 스키마에서 처음 한 번만 생성
CREATE TABLE PARCEL_DISPATCH (
    PARCEL_ID    NUMBER(4) PRIMARY KEY,
    HUB_CODE     VARCHAR2(2 CHAR) NOT NULL,
    RECIPIENT    VARCHAR2(30 CHAR) NOT NULL,
    FEE          NUMBER(8) NOT NULL,
    DISCOUNT     NUMBER(8),
    BOX_CODE     VARCHAR2(3 CHAR) NOT NULL,
    REQUESTED_AT DATE NOT NULL,
    CONSTRAINT CK_PD_FEE CHECK (FEE >= 0),
    CONSTRAINT CK_PD_DISCOUNT CHECK (DISCOUNT >= 0)
);

INSERT INTO PARCEL_DISPATCH VALUES (101, 'H1', '서연', 5500, NULL, '1',  DATE '2026-09-01');
INSERT INTO PARCEL_DISPATCH VALUES (102, 'H2', '민호', 7000, 500,  '10', DATE '2026-09-02');
INSERT INTO PARCEL_DISPATCH VALUES (103, 'H1', '지우', 7000, 1000, '2',  DATE '2026-09-03');
INSERT INTO PARCEL_DISPATCH VALUES (104, 'H3', '윤서', 5000, NULL, '1',  DATE '2026-09-01');
INSERT INTO PARCEL_DISPATCH VALUES (105, 'H2', '도윤', 7000, NULL, '2',  DATE '2026-09-04');
INSERT INTO PARCEL_DISPATCH VALUES (106, 'H1', '하린', 5500, 200,  '10', DATE '2026-09-03');
INSERT INTO PARCEL_DISPATCH VALUES (107, 'H3', '유진', 7000, 300,  '2',  DATE '2026-09-04');
INSERT INTO PARCEL_DISPATCH VALUES (108, 'H2', '지민', 5000, NULL, '1',  DATE '2026-09-02');
COMMIT;

-- 높은 배송비부터 표시하고, 배송비 동점이면 발송 ID 오름차순
SELECT PARCEL_ID, FEE
  FROM PARCEL_DISPATCH
 ORDER BY FEE DESC, PARCEL_ID ASC;

-- 방법 1: 테이블의 원래 컬럼명
SELECT PARCEL_ID, FEE
  FROM PARCEL_DISPATCH
 ORDER BY FEE DESC, PARCEL_ID ASC;

-- 방법 2: SELECT에서 만든 계산식의 별칭
SELECT PARCEL_ID,
       FEE - NVL(DISCOUNT, 0) AS NET_FEE
  FROM PARCEL_DISPATCH
 ORDER BY NET_FEE DESC, PARCEL_ID ASC;

-- 방법 3: 출력 목록의 순번 (1=PARCEL_ID, 3=FEE)
SELECT PARCEL_ID, HUB_CODE, FEE
  FROM PARCEL_DISPATCH
 ORDER BY 3 DESC, 1 ASC;

-- 1차: 거점 오름차순 / 2차: 배송비 내림차순 / 3차: 고유 ID 오름차순
SELECT PARCEL_ID, HUB_CODE, FEE, RECIPIENT
  FROM PARCEL_DISPATCH
 ORDER BY HUB_CODE ASC, FEE DESC, PARCEL_ID ASC;

-- 거점과 상관없이 배송비만 우선하면 순서가 달라진다.
SELECT PARCEL_ID, HUB_CODE, FEE
  FROM PARCEL_DISPATCH
 ORDER BY FEE DESC, HUB_CODE ASC, PARCEL_ID ASC;

-- Oracle 기본: ASC이면 NULL이 뒤
SELECT PARCEL_ID, DISCOUNT
  FROM PARCEL_DISPATCH
 ORDER BY DISCOUNT ASC, PARCEL_ID ASC;

-- Oracle 기본: DESC이면 NULL이 앞
SELECT PARCEL_ID, DISCOUNT
  FROM PARCEL_DISPATCH
 ORDER BY DISCOUNT DESC, PARCEL_ID ASC;

-- NULL이 있는 행을 맨 먼저 보여주기
SELECT PARCEL_ID, DISCOUNT
  FROM PARCEL_DISPATCH
 ORDER BY DISCOUNT ASC NULLS FIRST, PARCEL_ID ASC;

-- DESC이지만 NULL이 있는 행을 맨 뒤로 보내기
SELECT PARCEL_ID, DISCOUNT
  FROM PARCEL_DISPATCH
 ORDER BY DISCOUNT DESC NULLS LAST, PARCEL_ID ASC;

-- 문자코드: 일반적인 이진 정렬에서 '1', '10', '2'
SELECT DISTINCT BOX_CODE
  FROM PARCEL_DISPATCH
 ORDER BY BOX_CODE;

-- 중복 코드를 먼저 뽑은 뒤 숫자로 변환하여 1, 2, 10 순서
SELECT BOX_CODE
  FROM (SELECT DISTINCT BOX_CODE FROM PARCEL_DISPATCH)
 ORDER BY TO_NUMBER(BOX_CODE);

-- 집합 연산 결과는 최종 ORDER BY 한 번으로 정렬
SELECT PARCEL_ID AS ITEM_NO
  FROM PARCEL_DISPATCH
 WHERE HUB_CODE = 'H1'
UNION ALL
SELECT PARCEL_ID AS ITEM_NO
  FROM PARCEL_DISPATCH
 WHERE HUB_CODE = 'H3'
ORDER BY ITEM_NO DESC;

-- Oracle 12c 이상: 배송비가 높은 상위 3건
-- 동점일 때 PARCEL_ID로 순서를 확정하여 102, 103, 105를 반환
SELECT PARCEL_ID, FEE
  FROM PARCEL_DISPATCH
 ORDER BY FEE DESC, PARCEL_ID ASC
 FETCH FIRST 3 ROWS ONLY;

-- 특정 거점(H3)을 최우선으로 보이게 하는 조건식 정렬
SELECT PARCEL_ID, HUB_CODE, FEE
  FROM PARCEL_DISPATCH
 ORDER BY CASE WHEN HUB_CODE = 'H3' THEN 0 ELSE 1 END,
          FEE DESC, PARCEL_ID ASC;