WITH PROD AS( SELECT 'P1' PRODUCTID,'PN1' PRODUCTNAME,'SUPL1' SUPPLIERID,'CAT1' CATEGORYID,5 UNIT,5000 PRICE FROM DUAL UNION ALL SELECT 'P2','PN2','SUPL1','CAT1',5,4000 FROM DUAL UNION ALL SELECT 'P3','PN3','SUPL1','CAT2',5,6000 FROM DUAL UNION ALL SELECT 'P4','PN4','SUPL1','CAT2',5,7000 FROM DUAL ) SELECT CATEGORYID , PRODUCTID , PRODUCTNAME , PRICE FROM ( SELECT CATEGORYID , PRODUCTID , PRODUCTNAME , PRICE , ROW_NUMBER() OVER(PARTITION BY CATEGORYID ORDER BY PRICE DESC) RK FROM PROD A ) WHERE RK=1;
mysql은 문법이 다를 수 있습니다 oracle 기준입니다
참고)