아래와 같은 데이터가 있어요
부서 ID 부서발령일
1 20130513
1 20130503
3 20130413
4 20130302
4 20130301
2 20130220
2 20130211
1 20130130
1 20130128
1 20130102
위의 데이터중 Bold처리된 것만 뽑을 방법이 없을까요? 환경은 9i입니다.
WITH t AS ( SELECT 1 empno, 1 deptno, '20130513' dt FROM dual UNION ALL SELECT 1, 1, '20130503' FROM dual UNION ALL SELECT 1, 3, '20130413' FROM dual UNION ALL SELECT 1, 4, '20130302' FROM dual UNION ALL SELECT 1, 4, '20130301' FROM dual UNION ALL SELECT 1, 2, '20130220' FROM dual UNION ALL SELECT 1, 2, '20130211' FROM dual UNION ALL SELECT 1, 1, '20130130' FROM dual UNION ALL SELECT 1, 1, '20130128' FROM dual UNION ALL SELECT 1, 1, '20130102' FROM dual ) SELECT empno , deptno , MIN(dt) dt FROM (SELECT empno, deptno, dt , ROW_NUMBER() OVER(PARTITION BY empno ORDER BY dt) rn1 , ROW_NUMBER() OVER(PARTITION BY empno, deptno ORDER BY dt) rn2 FROM t ) GROUP BY empno, deptno, rn1 - rn2 ORDER BY empno, dt DESC ;
너무나 감사해요~ 좋은 방법 또 얻어 갑니다~~ 즐거운 하루 되세요~
