[SQL] Database Custom Order By (Oracle/Tibero, MySQL, PostgreSQL)

기본 정렬 방식을 떠나 특정 컬럼의 특정 값, 혹은 값의 순서를 커스텀하고 싶을 때 각 Database 마다 어떻게 사용되는 지 살펴보자

 

예제

컬럼 명 : COLUMN1

허용 값 : 검정, 노랑, 빨강, 파랑

 

Tibero / Oracle

아래 두 방법 모두 IF ELSE 문의 형태와 유사하다.

CASE WHEN 문

SELECT * 
  FROM TBL
 ORDER BY (CASE WHEN COLUMN1 = '빨강' THEN 1
                WHEN COLUMN1 = '검정' THEN 2
                WHEN COLUMN1 = '파랑' THEN 3 
                ELSE 4 END) ASC -- 혹은 DESC

COLUMN1의 값이 빨강일 경우는 1 값으로 대체, 검정일 경우는 2 값으로 대체, 파랑일 경우는 3 값으로 대체, 나머지(노랑)일 경우 4 값으로 대체돼서 1,2,3,4 숫자 값을 기준으로 정렬이 된다.

DECODE 함수 

SELECT * 
  FROM TBL
 ORDER BY (DECODE(COLUMN1, '빨강', 1, '검정', 2, '파랑', 3, 4)) ASC -- 혹은 DESC

DECODE(컬럼명, 조건1, 조건1의 값, 조건2, 조건2의 값, ...., 나머지의 값) 구문으로 작성하면 된다.

 

MySQL

FIELD 함수

SELECT * 
  FROM TBL
 ORDER BY FIELD(COLUMN1, '빨강', '검정', '파랑', '노랑') ASC -- 혹은 DESC

 

PostgreSQL

9.5 이하의 버전일 경우 CASE WHEN 구문 사용하여 구현 가능

9.5 이상의 버전일 경우 ARRAY_POSITION 함수 사용 가능

ARRAY_POSITION 함수

SELECT * 
  FROM TBL
 ORDER BY ARRAY_POSITION(ARRAY['빨강', '검정', '파랑', '노랑'], COLUMN1::text) ASC -- 혹은 DESC

ARRAY_POSITION(array, element [, integer])

• array : 배열 입력
• element : 찾을 요소 값 입력
• integer : 선택적 인수로, 시작할 위치를 지정한다

이 때 주의할 점은 array에 들어가는 데이터 타입과 조회를 하고자 하는 컬럼의 타입이 동일해야 한다.

SELECT * 
  FROM TBL
 ORDER BY ARRAY_POSITION(ARRAY['빨강', '검정', '파랑', '노랑'], COLUMN1::int) -- ERROR