테이블A
위와 같은 테이블이 있는 경우
A코드인 것중에서 수량이 가장 적은 걸 찾아서 해당 ROW의 색깔을 가져오고 싶은데
어떤식으로 쿼리를 작성해야할지 도움 부탁드립니다.
얻고 싶은 결과값
TEST1과 TEST2 비교해서 참고 하시면 될거 같습니다.
WITH TEMP (CODE, QTY, COLOR) AS ( VALUES ('A',10,'RED'),('A',20,'YELLOS'),('B',10,'WHITE'),('B',20,'BLACK') ) , TEST1 AS ( SELECT B1.CODE, B1.QTY, B1.COLOR FROM ( SELECT A.CODE, MIN(A.QTY) AS MINQTY FROM TEMP A GROUP BY A.CODE ) B JOIN TEMP B1 ON B.CODE = B1.CODE AND B.MINQTY = B1.QTY ) , TEST2 AS ( SELECT A.CODE, A.QTY, A.COLOR FROM TEMP A WHERE (A.CODE, A.QTY) IN (SELECT B.CODE, MIN(B.QTY) AS MINQTY FROM TEMP B GROUP BY B.CODE) ) --SELECT * FROM TEST1; SELECT * FROM TEST2;
WITH TEMP (CODE, QTY, COLOR) AS ( VALUES ('A',10,'RED'),('A',20,'YELLOS'),('B',10,'WHITE'),('B',20,'BLACK') ) SELECT B.CODE , B.QTY , B.COLOR FROM (SELECT A.CODE , A.QTY , A.COLOR , ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY QTY ) AS RANKNUM FROM TEMP A ) B WHERE B.RANKNUM = 1 WITH UR;