WITH TST AS ( SELECT 1 SEQ, '1111' ID, 'B' GB FROM DUAL UNION SELECT 2, '2222', 'A' FROM DUAL UNION SELECT 3, '3333', '' FROM DUAL UNION SELECT 4, '4444', 'B' FROM DUAL UNION SELECT 5, '5555', '' FROM DUAL ) SELECT TST.* FROM TST 결과값 SEQ ID GB 1 1111 B 4 4444 B 2 2222 A 3 3333 5 5555
위와 같은 형태로 출력하고 싶습니다.
SEQ가 작은 값이 먼저 노출되고 GB가 같은 건은 바로 아래 row에 노출되게 하고 싶은데
ORDER BY를 어떻게 줘야 하는지 고민되어 문의 드립니다. 고수님들의 조언 부탁 드립니다.
WITH TST AS ( SELECT 1 SEQ, '1111' ID, 'B' GB FROM DUAL UNION SELECT 2, '2222', '' FROM DUAL UNION SELECT 3, '3333', 'A' FROM DUAL UNION SELECT 4, '4444', 'B' FROM DUAL UNION SELECT 5, '5555', '' FROM DUAL ) SELECT TST.* FROM TST ORDER BY GB DESC NULLS LAST , SEQ ASC
예를 들어 2, 3번의 GB값을 바꾸면 해당 ORDER BY로는 적용되지 않습니다.
SEQ가 우선이고 같은 GB에 있는 ID가 밑에 노출되도록 하고자 합니다.
WITH TST AS ( SELECT 1 SEQ, '1111' ID, 'B' GB FROM DUAL UNION SELECT 2, '2222', 'A' FROM DUAL UNION SELECT 3, '3333', '' FROM DUAL UNION SELECT 4, '4444', 'B' FROM DUAL UNION SELECT 5, '5555', '' FROM DUAL ) SELECT * FROM TST order by min(seq) over(partition by gb), seq ; -- order by 부분 정정합니다. order by decode(gb, null, seq, min(seq) over(partition by gb)), seq
WITH TST AS ( SELECT 1 SEQ, '1111' ID, 'B' GB FROM DUAL UNION SELECT 2, '2222', 'A' FROM DUAL UNION SELECT 3, '3333', '' FROM DUAL UNION SELECT 4, '4444', 'B' FROM DUAL UNION SELECT 5, '5555', '' FROM DUAL ) SELECT seq, id, gb FROM ( SELECT TST.* , CASE WHEN COUNT(gb) OVER(PARTITION BY gb) > 1 THEN MIN(SEQ) OVER(PARTITION BY gb) ELSE 999 END gb2 FROM TST ) ORDER BY gb2, seq