데이터베이스 질문과 답변
MAX 함수 이용한 조회 쿼리 질문 있습니다.
0
7
1,110
by 개발2년차 [2016.06.02 11:34:13]
WITH T AS ( SELECT '200909' PRD, 'A' GRP, '002' VER , '1' TRANS FROM DUAL UNION SELECT '200909' PRD, 'A' GRP, '004' VER , '2' TRANS FROM DUAL UNION SELECT '200909' PRD, 'A' GRP, '006' VER , '3' TRANS FROM DUAL UNION SELECT '200909' PRD, 'B' GRP, '004' VER , '2' TRANS FROM DUAL UNION SELECT '200909' PRD, 'B' GRP, '006' VER , '1' TRANS FROM DUAL UNION SELECT '201003' PRD, 'C' GRP, '002' VER , '1' TRANS FROM DUAL UNION SELECT '201003' PRD, 'C' GRP, '008' VER , '2' TRANS FROM DUAL)
위와 같은 테이블이 있을 때 PRD - GRP 별로 TRANS가 가장 높은 순위의 VER을 뽑아내고 싶습니다.
#결과
200909 A 006 3
200909 B 004 2
201003 C 008 2
MAX 함수를 가지고 짜려 했으나 잘 안되네요 ㅠ
조언 부탁드립니다.
by DONKEY
[2016.06.02 11:54:19]
WITH T AS (
SELECT '200909' PRD, 'A' GRP, '002' VER , '1' TRANS FROM DUAL
UNION
SELECT '200909' PRD, 'A' GRP, '004' VER , '2' TRANS FROM DUAL
UNION
SELECT '200909' PRD, 'A' GRP, '006' VER , '3' TRANS FROM DUAL
UNION
SELECT '200909' PRD, 'B' GRP, '004' VER , '2' TRANS FROM DUAL
UNION
SELECT '200909' PRD, 'B' GRP, '006' VER , '1' TRANS FROM DUAL
UNION
SELECT '201003' PRD, 'C' GRP, '002' VER , '1' TRANS FROM DUAL
UNION
SELECT '201003' PRD, 'C' GRP, '008' VER , '2' TRANS FROM DUAL)
SELECT *
FROM T
WHERE (PRD,GRP,TRANS) IN (SELECT PRD,GRP,MAX(TRANS)
FROM T
GROUP BY PRD,GRP)
예전에 비슷한 질문을 게시판에서 봤던 기억이나서 마농님이 짜신 쿼리 보고 응용해봤습니다 ㅎㅎㅎ
by jkson
[2016.06.02 12:19:11]
SELECT PRD, GRP, VER, TRANS FROM ( SELECT PRD, GRP, VER, TRANS, ROW_NUMBER() OVER(PARTITION BY PRD, GRP ORDER BY TRANS DESC) RN FROM T ) WHERE RN = 1
이런 방법도 있어요.
by DONKEY
[2016.06.02 12:48:39]
우왕 ㅋㅋㅋ 감사합니다
by 개발2년차
[2016.06.02 13:13:17]
두 분 모두 감사합니다.
큰 공부가 되었습니다 *^^*
by 우리집아찌
[2016.06.02 13:16:34]
WITH T AS (
SELECT '200909' PRD, 'A' GRP, '002' VER , '1' TRANS FROM DUAL
UNION
SELECT '200909' PRD, 'A' GRP, '004' VER , '2' TRANS FROM DUAL
UNION
SELECT '200909' PRD, 'A' GRP, '006' VER , '3' TRANS FROM DUAL
UNION
SELECT '200909' PRD, 'B' GRP, '004' VER , '2' TRANS FROM DUAL
UNION
SELECT '200909' PRD, 'B' GRP, '006' VER , '1' TRANS FROM DUAL
UNION
SELECT '201003' PRD, 'C' GRP, '002' VER , '1' TRANS FROM DUAL
UNION
SELECT '201003' PRD, 'C' GRP, '008' VER , '2' TRANS FROM DUAL)
SELECT MAX(PRD)
, MAX(GRP)
, MAX(VER) KEEP (DENSE_RANK FIRST ORDER BY PRD , GRP , TRANS DESC) VER
, MAX(TRANS) KEEP (DENSE_RANK FIRST ORDER BY PRD , GRP , TRANS DESC) TRANS
FROM T
GROUP BY PRD , GRP
ORDER BY PRD , GRP
by 마농
[2016.06.02 13:43:54]
SELECT prd , grp , MAX(ver) KEEP (DENSE_RANK FIRST ORDER BY trans DESC) ver , MAX(trans) trans FROM t GROUP BY prd, grp ORDER BY prd, grp ;
by 우리집아찌
[2016.06.02 17:03:28]
제가 생각없이 짰네요. ㅡㅡ;
) 버튼을 클릭하여 작성 하시면 됩니다.