oracle如何获取最大时间的整条记录
Table
ID PID TIME UPDATETIME
----------------------------------------
1 2 20:11 2012-02-26
2 2 20:20 2012-02-27
3 2 20:20 2012-02-27
4 3 19:12 2012-02-27
5 3 18:16 2012-02-27
6 4 15:45 2012-02-28
7 4 15:30 2012-02-29
8 5 06:05 2012-02-29
-----------------------------------------
我想取得每个PID中的UPDATETIME字段最大值的数据不知道如何是好啊
下面这个语句也不行, 因为最大时间有时候对应两条记录呢
SQL code
select * from Table a where a.UPDATETIME=(select max(UPDATETIME) from Table where PID=a.PID)
求教各位大神,帮助看看呀,
(好似我全部分都拿出来了~,大哥们行行好啊)
------解决方案--------------------select * from table where to_date(UPDATETIME || time || ':00','yyyy-mm-dd hh24:mi:ss')=(
select max(to_date(UPDATETIME || time || ':00','yyyy-mm-dd hh24:mi:ss')) from table )
------解决方案--------------------select pid,max(updatetime) from test8 group by pid
这个不行吗?
------解决方案--------------------select t.* from tb t where UPDATETIME = (select max(UPDATETIME) from tb where pid = t.pid)
select t.* from tb t where not exists (select 1 from tb where pid = t.pid and UPDATETIME > t.UPDATETIME)
------解决方案--------------------如果最大时间有时候对应两条记录的话,再以哪个为标准?
假设为id,取最大的哪个?
select t.* from tb t where not exists (select 1 from tb where pid = t.pid and (UPDATETIME > t.UPDATETIME or (UPDATETIME = t.UPDATETIME and id > t.id) ))
------解决方案--------------------使用ROWID 取唯一记录
------解决方案--------------------SQL code
select *
from (select t.*, row_number() over (order by datetime desc) as rnum
from tbl t)
where rnum = 1;
------解决方案--------------------
SQL code
select pid,max(UPDATETIME ) from table group by pid