[퀴즈] 그룹별 (홀수/짝수)행 데이터만 검색하기

다음과 같은 집합에서 그룹별 이름으로 정렬하여 다음 자료를 출력하세요.
문제1. 홀수행 자료만 검색하세요.
문제2. 짝수행 자료만 검색하세요. 단, 짝수행이 없어도 그룹명은 나와야 합니다.

WITH t AS
(
SELECT 1 grp, 'A' nm FROM dual
UNION ALL SELECT 1, 'B' FROM dual
UNION ALL SELECT 1, 'C' FROM dual
UNION ALL SELECT 2, 'D' FROM dual
UNION ALL SELECT 2, 'E' FROM dual
UNION ALL SELECT 3, 'F' FROM dual
UNION ALL SELECT 4, 'G' FROM dual
UNION ALL SELECT 4, 'H' FROM dual
UNION ALL SELECT 4, 'I' FROM dual
UNION ALL SELECT 4, 'J' FROM dual
)
SELECT * FROM t;
 

정답은 다시 올리겠습니다.

[정답 : 홀수] <=== 트리플클릭
SELECT grp
    , nm
FROM
(
SELECT nm, grp
    , ROW_NUMBER() OVER(PARTITION BY grp ORDER BY nm) rn
FROM t
)
WHERE MOD(rn, 2) = 1
;

[정답 : 짝수] <=== 트리플클릭
SELECT grp
    , DECODE(rn,1,'',nm) nm
FROM
(
SELECT grp, nm
    , ROW_NUMBER() OVER(PARTITION BY grp ORDER BY nm) rn
    , COUNT(*) OVER(PARTITION BY grp) cnt
FROM t
)
WHERE MOD(rn, 2) = 0
   OR cnt = 1
ORDER BY grp, nm
;

[정답 : 홀짝구분] <=== 트리플클릭
SELECT grp
    , DECODE(:gubun||rn,'01',null,nm) nm
  FROM
(
SELECT grp, nm
    , ROW_NUMBER() OVER(PARTITION BY grp ORDER BY nm) rn
    , COUNT(*) OVER(PARTITION BY grp) cnt
FROM t
)
WHERE MOD(rn, 2) = :gubun -- (구분 - 1:홀수, 0:짝수)
   OR cnt = 1
ORDER BY grp, nm
;