[Oracle] 그룹별 최종일자 ROW 가져오기

[Oracle] 그룹별 최종일자 ROW 가져오기

WITH TEMP AS ( SELECT 'A' GRP, TO_DATE('2018-11-01','YYYY-MM-DD') DT, 50 AMT FROM DUAL UNION ALL SELECT 'A' GRP, TO_DATE('2018-11-02','YYYY-MM-DD') DT, 100 AMT FROM DUAL UNION ALL SELECT 'A' GRP, TO_DATE('2018-11-03','YYYY-MM-DD') DT, 70 AMT FROM DUAL UNION ALL SELECT 'A' GRP, TO_DATE('2018-11-03','YYYY-MM-DD') DT, 150 AMT FROM DUAL UNION ALL SELECT 'B' GRP, TO_DATE('2018-11-05','YYYY-MM-DD') DT, 200 AMT FROM DUAL UNION ALL SELECT 'B' GRP, TO_DATE('2018-11-02','YYYY-MM-DD') DT, 220 AMT FROM DUAL UNION ALL SELECT 'B' GRP, TO_DATE('2018-11-02','YYYY-MM-DD') DT, 240 AMT FROM DUAL UNION ALL SELECT 'B' GRP, TO_DATE('2018-11-02','YYYY-MM-DD') DT, 210 AMT FROM DUAL UNION ALL SELECT 'C' GRP, TO_DATE('2018-10-02','YYYY-MM-DD') DT, 210 AMT FROM DUAL ) SELECT T.GRP, T.DT, T.AMT, T.RNK FROM ( SELECT GRP, DT, AMT, DENSE_RANK() OVER (PARTITION BY GRP ORDER BY DT DESC) RNK FROM TEMP ) T WHERE T.RNK = 1;

< 응용 >

WITH TEMP AS ( SELECT 'A' GRP, TO_DATE('2018-11-01','YYYY-MM-DD') DT, 50 AMT FROM DUAL UNION ALL SELECT 'A' GRP, TO_DATE('2018-11-02','YYYY-MM-DD') DT, 100 AMT FROM DUAL UNION ALL SELECT 'A' GRP, TO_DATE('2018-11-03','YYYY-MM-DD') DT, 70 AMT FROM DUAL UNION ALL SELECT 'A' GRP, TO_DATE('2018-11-03','YYYY-MM-DD') DT, 150 AMT FROM DUAL UNION ALL SELECT 'B' GRP, TO_DATE('2018-11-05','YYYY-MM-DD') DT, 200 AMT FROM DUAL UNION ALL SELECT 'B' GRP, TO_DATE('2018-11-02','YYYY-MM-DD') DT, 220 AMT FROM DUAL UNION ALL SELECT 'B' GRP, TO_DATE('2018-11-02','YYYY-MM-DD') DT, 240 AMT FROM DUAL UNION ALL SELECT 'B' GRP, TO_DATE('2018-11-02','YYYY-MM-DD') DT, 210 AMT FROM DUAL UNION ALL SELECT 'C' GRP, TO_DATE('2018-10-02','YYYY-MM-DD') DT, 210 AMT FROM DUAL ) SELECT T.GRP, T.DT, SUM(T.AMT) FROM ( SELECT GRP, DT, AMT, DENSE_RANK() OVER (PARTITION BY GRP ORDER BY DT DESC) RNK FROM TEMP ) T WHERE T.RNK = 1 GROUP BY T.GRP, T.DT