001 2011/07/11 16:18:23 0 002 2011/07/11 16:26:14 195.4 001 2011/07/11 16:18:24 0 002 2011/07/11 16:26:13 138.8
:
001 2011/07/11 16:18:24 0 002 2011/07/11 16:26:14 195.4
해당 코드가 중복시 가장 나중값 (날짜시간을기준) 1개를 을 가지고 오고 싶습니다.
WITH TEST1 AS ( SELECT '001' CODE, ' 2011/07/11 16:18:23' DT, ' 1' VAL FROM DUAL UNION ALL SELECT '002', ' 2011/07/11 16:26:14' , ' 195.4' FROM DUAL UNION ALL SELECT '001', ' 2011/07/11 16:18:24' , ' 2' FROM DUAL UNION ALL SELECT '002', ' 2011/07/11 16:26:15' , ' 138.8' FROM DUAL UNION ALL SELECT '001', ' 2011/07/11 16:18:25' , ' 3' FROM DUAL UNION ALL SELECT '002', ' 2011/07/11 16:26:16' , ' 188.8' FROM DUAL ) SELECT CD, DT2, (SELECT VAL FROM TEST1 WHERE CODE = CD AND DT = DT2) VAL FROM ( SELECT CODE CD, MAX(DT) DT2 FROM TEST1 A GROUP BY CODE )
WITH TEST1 AS ( SELECT '001' CODE, ' 2011/07/11 16:18:23' DT, ' 1' VAL FROM DUAL UNION ALL SELECT '002', ' 2011/07/11 16:26:14' , ' 195.4' FROM DUAL UNION ALL SELECT '001', ' 2011/07/11 16:18:24' , ' 2' FROM DUAL UNION ALL SELECT '002', ' 2011/07/11 16:26:15' , ' 138.8' FROM DUAL UNION ALL SELECT '001', ' 2011/07/11 16:18:25' , ' 3' FROM DUAL UNION ALL SELECT '002', ' 2011/07/11 16:26:16' , ' 188.8' FROM DUAL )
select * from( select row_number() over (partition by code order by dt desc) rn ,code,dt from test1 ) t where rn=1