WITH T ( "거래처" ,"품목", "단가" ) AS ( SELECT 'A','AA',100 FROM DUAL UNION ALL SELECT 'B','AA',200 FROM DUAL UNION ALL SELECT 'C','AA',300 FROM DUAL UNION ALL SELECT 'A','BB',300 FROM DUAL UNION ALL SELECT 'B','BB',200 FROM DUAL UNION ALL SELECT 'C','BB',100 FROM DUAL ) SELECT * FROM (SELECT T.* , RANK() OVER(ORDER BY 단가 ) RN FROM T ) WHERE RN = 1
-- 다시 올립니다. -- 품목별 최저 단가가 두개일때는 거래처 선책은 어떻게 해야하나요? WITH T ( "거래처" ,"품목", "단가" ) AS ( SELECT 'A','AA',100 FROM DUAL UNION ALL SELECT 'B','AA',200 FROM DUAL UNION ALL SELECT 'C','AA',300 FROM DUAL UNION ALL SELECT 'A','BB',300 FROM DUAL UNION ALL SELECT 'B','BB',200 FROM DUAL UNION ALL SELECT 'C','BB',100 FROM DUAL ) SELECT * FROM (SELECT T.* , ROW_NUMBER() OVER(PARTITION BY 품목 ORDER BY 단가 ) RN FROM T ) WHERE RN = 1