一道sql题目。。。去重复 sql题目--oracle
表a中 有2个日期字段 一个是updateDate 一个是lastDate
例如:表结构是:
name updateDate lastDate
数据可能是
name updateDate last
one 2013/2/21 14:37:17 2013/2/28 14:37:17
two 2013/2/28 14:37:17 2013/2/28 14:37:17
one 2013/2/23 14:37:17 2013/2/21 14:37:17
one 2013/2/28 14:37:17 2013/2/28 14:37:17
。。。。。。
select distinct(name),updatetime,lasttime from tabletest a
where
a.updatetime =
(
select max(b.updatetime) from tabletest b
where a.name= b.name
)
and a.lasttime =TO_DATE('2013/2/28 14:37:17','yyyy/mm/dd hh24:mi:ss')
; ------解决方案-------------------- with tt as(
select name,updateDate,lastDate,row_number() over(partition by name,lastDate order by updateDate desc) rn from tab_name
where lastDate='2013/2/28 14:37:17'
)
select name,updateDate,lastDate from tt where rn=1;