ORCLE 取当前时间往前最近一条记录，并通过group by 分组。

[a380346189]

需求：表中三个字段 tagname tagvalue timetamp
需要取出当前时间往前5分钟最近的一条记录的tagvalue值, 并且通过tagname分组。

比如:
tagname tagvalue timetamp
A 5 2017-02-24 10:56:00
A 6 2017-02-24 10:58:00
B 2 2017-02-24 10:56:00
B 6 2017-02-24 10:58:00
C 5 2017-02-24 10:56:00
C 3 2017-02-24 10:58:00

需要取成
tagname tagvalue timetamp
A 6 2017-02-24 10:58:00
B 6 2017-02-24 10:58:00
C 3 2017-02-24 10:58:00

就是说，不仅要取通过tagname 分组，并且分组后的tagvalue值要取 时间最近的那一条记录。



本人写的sql 卡写到这找不到解决办法了；

select tagname, tagvalue from ( --这里的tagvalue 怎样能取到第一条呢？而不是最大 也不是最小。
select *
  from jcsj_sssj
 where timetamp  between to_date('2017-02-16 23:55:00', 'yyyy-mm-dd hh24:mi:ss')
    and to_date('2017-02-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
--and tagname = 'OPC-61-BS.BS_PLC.PLC.12PP_PT565' 
order by timetamp desc
)
group by tagname

求大神给出解决方案。

[xiaoguanzhao]

with jcsj_sssj as ( select 'A' tagname,'5' tagvalue, '2017-02-24 10:56:00' timetamp from dual union all select 'A' tagname,'6' tagvalue, '2017-02-24 10:58:00' timetamp from dual union all select 'B' tagname,'2' tagvalue, '2017-02-24 10:56:00' timetamp from dual union all select 'B' tagname,'6' tagvalue, '2017-02-24 10:58:00' timetamp from dual union all select 'C' tagname,'5' tagvalue, '2017-02-24 10:56:00' timetamp from dual union all select 'C' tagname,'3' tagvalue, '2017-02-24 10:58:00' timetamp from dual ) --需要取出当前时间往前5分钟最近的一条记录的tagvalue值, 并且通过tagname分组。 select * from jcsj_sssj where (tagname,timetamp) in (select tagname,timetamp from ( select tagname,spanSeconds ,rank() over(partition by tagname order by spanSeconds ) as num ,timetamp from ( select floor(to_number(sysdate-to_date(timetamp,'yyyy-mm-dd hh24:mi:ss'))*24*60*60)+300 as spanSeconds ,tagname,timetamp from jcsj_sssj ) ) t where t.num=1)

[落落叶叶无声]

SELECT TAGNAME, TAGVALUE, TIMETAMP
  FROM (SELECT TAGNAME,
               TAGVALUE,
               TIMETAMP,
               ROW_NUMBER() OVER(PARTITION BY TAGNAME ORDER BY TIMETAMP DESC) RN
          FROM jcsj_sssj
         WHERE TIMETAMP BETWEEN
               SYSDATE - 5 / (24 * 60) AND SYSDATE)
 WHERE RN = 1;