게시일: May 22, 2012 12:4:12 AM
- 관련 동영상
http://www.screencast.com/t/YomfPXaSZd
* 아래의 내용을 테이블이라고 가정
select *
from (
select to_date('2012-5-16', 'yyyy-mm-dd') dt, '0001' pid, 1 seq from dual union all
select to_date('2012-5-16', 'yyyy-mm-dd') dt, '0002' pid, 1 seq from dual union all
select to_date('2012-5-17', 'yyyy-mm-dd') dt, '0001' pid, 1 seq from dual union all
select to_date('2012-5-17', 'yyyy-mm-dd') dt, '0001' pid, 2 seq from dual union all
select to_date('2012-5-17', 'yyyy-mm-dd') dt, '0001' pid, 3 seq from dual union all
select to_date('2012-5-18', 'yyyy-mm-dd') dt, '0001' pid, 1 seq from dual union all
select to_date('2012-5-18', 'yyyy-mm-dd') dt, '0001' pid, 2 seq from dual
)
;
------------------------------------
DT PID SEQ
------------------------------------
2012-05-16 00:00:00 0001 1
2012-05-16 00:00:00 0002 1
2012-05-17 00:00:00 0001 1
2012-05-17 00:00:00 0001 2
2012-05-17 00:00:00 0001 3
2012-05-18 00:00:00 0001 1
2012-05-18 00:00:00 0001 2 --> 가장 마지막에 처리된 데이터
------------------------------------
: 각 컬럼의 최대값
: 각 로우의 최대값
* 문제 *
가장 마지막에 처리된 데이터를 조회하세요!
1-1. 실수
with a as (
select to_date('2012-5-16', 'yyyy-mm-dd') dt, '0001' pid, 1 seq from dual union all
select to_date('2012-5-16', 'yyyy-mm-dd') dt, '0002' pid, 1 seq from dual union all
select to_date('2012-5-17', 'yyyy-mm-dd') dt, '0001' pid, 1 seq from dual union all
select to_date('2012-5-17', 'yyyy-mm-dd') dt, '0001' pid, 2 seq from dual union all
select to_date('2012-5-17', 'yyyy-mm-dd') dt, '0001' pid, 3 seq from dual union all
select to_date('2012-5-18', 'yyyy-mm-dd') dt, '0001' pid, 1 seq from dual union all
select to_date('2012-5-18', 'yyyy-mm-dd') dt, '0001' pid, 2 seq from dual
)
select *
from a
where (a.dt, a.pid, a.seq) in (select max(a.dt), max(a.pid), max(a.seq) from a)
;
-> 데이터가 조회되지 않는다.
1-2. 이유
with a as (
select to_date('2012-5-16', 'yyyy-mm-dd') dt, '0001' pid, 1 seq from dual union all
select to_date('2012-5-16', 'yyyy-mm-dd') dt, '0002' pid, 1 seq from dual union all
select to_date('2012-5-17', 'yyyy-mm-dd') dt, '0001' pid, 1 seq from dual union all
select to_date('2012-5-17', 'yyyy-mm-dd') dt, '0001' pid, 2 seq from dual union all
select to_date('2012-5-17', 'yyyy-mm-dd') dt, '0001' pid, 3 seq from dual union all
select to_date('2012-5-18', 'yyyy-mm-dd') dt, '0001' pid, 1 seq from dual union all
select to_date('2012-5-18', 'yyyy-mm-dd') dt, '0001' pid, 2 seq from dual
)
select max(a.dt), max(a.pid), max(a.seq) from a -- 각 컬럼의 max 값을 찾음
;
----------------------------------------------
MAX(A.DT) MAX(A.PID) MAX(A.SEQ)
----------------------------------------------
2012-05-18 00:00:00 0002 3
----------------------------------------------
2-1. 해결
with a as (
select to_date('2012-5-18', 'yyyy-mm-dd') dt, '0001' pid, 1 seq from dual union all
select to_date('2012-5-16', 'yyyy-mm-dd') dt, '0002' pid, 1 seq from dual union all
select to_date('2012-5-18', 'yyyy-mm-dd') dt, '0001' pid, 2 seq from dual union all
select to_date('2012-5-17', 'yyyy-mm-dd') dt, '0002' pid, 1 seq from dual union all
select to_date('2012-5-17', 'yyyy-mm-dd') dt, '0002' pid, 2 seq from dual union all
select to_date('2012-5-17', 'yyyy-mm-dd') dt, '0002' pid, 3 seq from dual union all
select to_date('2012-5-16', 'yyyy-mm-dd') dt, '0003' pid, 1 seq from dual
)
select *
from a
where a.dt||a.pid||a.seq = (select max(dt||pid||seq) from a)
;
-----------------------------------
DT PID SEQ
-----------------------------------
2012-05-18 00:00:00 0001 2
-----------------------------------
2-2. 이유
with a as (
select to_date('2012-5-18', 'yyyy-mm-dd') dt, '0001' pid, 1 seq from dual union all
select to_date('2012-5-16', 'yyyy-mm-dd') dt, '0002' pid, 1 seq from dual union all
select to_date('2012-5-18', 'yyyy-mm-dd') dt, '0001' pid, 2 seq from dual union all
select to_date('2012-5-17', 'yyyy-mm-dd') dt, '0002' pid, 1 seq from dual union all
select to_date('2012-5-17', 'yyyy-mm-dd') dt, '0002' pid, 2 seq from dual union all
select to_date('2012-5-17', 'yyyy-mm-dd') dt, '0002' pid, 3 seq from dual union all
select to_date('2012-5-16', 'yyyy-mm-dd') dt, '0003' pid, 1 seq from dual
)
select max(dt||pid||seq) from a -- 각 로우의 max 값을 찾음
;
-------------------------
MAX(DT||PID||SEQ)
-------------------------
2012-05-18 00:00:0000012
-------------------------