WITH T (v1,v2,v3) AS ( SELECT 'A','a','1' FROM DUAL UNION ALL SELECT 'A','a','2' FROM DUAL UNION ALL SELECT 'A','a','3' FROM DUAL UNION ALL SELECT 'A','b','1' FROM DUAL UNION ALL SELECT 'A','b','2' FROM DUAL UNION ALL SELECT 'B','c','1' FROM DUAL UNION ALL SELECT 'B','c','2' FROM DUAL UNION ALL SELECT 'B','d','1' FROM DUAL UNION ALL SELECT 'B','d','2' FROM DUAL UNION ALL SELECT 'B','d','3' FROM DUAL ) SELECT CASE WHEN ROW_NUMBER() OVER(PARTITION BY v1 ORDER BY num ) = 1 THEN v1 END v1 ,CASE WHEN ROW_NUMBER() OVER(PARTITION BY v1,v2 ORDER BY num ) = 1 THEN v2 END v2 ,v3 FROM (SELECT T.*, rownum num FROM T)
감사합니다 !!! ㅎㅎ 쿼리 응용해서 하니 잘되네요 !!
-- Rownum 을 이용하는 건 좀 이상하네요. -- SELECT CASE WHEN ROW_NUMBER() OVER(PARTITION BY v1 ORDER BY v2, v3) = 1 THEN v1 END v1 , CASE WHEN ROW_NUMBER() OVER(PARTITION BY v1, v2 ORDER BY v3) = 1 THEN v2 END v2 , v3 FROM t ; SELECT NULLIF(v1, LAG(v1) OVER(ORDER BY v1, v2, v3)) v1 , NULLIF(v2, LAG(v2) OVER(PARTITION BY v1 ORDER BY v2, v3)) v2 , v3 FROM t ;