请教一sql语句的写法
我有一个数据表，格式如下，每个senid的发送时间不是相同的。
dataId senid senValue time
***** 1 100 3:45:00
***** 1 100 3:40:00
***** 1 100 3:35:00
***** 1 100 3:30:00
***** 2 2000 3:45:00
***** 2 2000 3:44:00
***** 2 2000 3:43:00
***** 2 2000 3:42:00
***** 3 300 3:44:50
***** 3 300 3:44:40
***** 3 300 3:44:30
***** 3 300 3:44:20
要查找每个 senid 的最新数据,得到的结果应该如下所示
senid senValue time
1 100 3:45:00
2 2000 3:45:00
3 300 3:44:50
就是这样，查找出来每个senid的最新数据和时间。请问这个sql语句应该怎么写比较好？
oracle sql
[解决办法]
with t as
(
select 1 sendid, 100 sendv, '3:45:00' sendt from dual
union all
select 1 sendid, 100 sendv, '3:40:00' sendt from dual
union all
select 2 sendid, 200 sendv, '3:45:00' sendt from dual
union all
select 2 sendid, 200 sendv, '3:40:00' sendt from dual
union all
select 3 sendid, 300 sendv, '3:45:50' sendt from dual
union all
select 3 sendid, 300 sendv, '3:45:40' sendt from dual
)
select sendid,sendv, max(sendt) from t group by sendid, sendv
select sendid, max(sendt),max(sendv)keep(dense_rank first order by sendt desc ) sendt from t group by sendid
with t as
(select 1 sendid, 100 sendv, '3:45:00' sendt
from dual
union all
select 1 sendid, 100 sendv, '3:40:00' sendt
from dual
union all
select 2 sendid, 200 sendv, '3:45:00' sendt
from dual
union all
select 2 sendid, 200 sendv, '3:40:00' sendt
from dual
union all
select 3 sendid, 300 sendv, '3:45:50' sendt
from dual
union all
select 3 sendid, 300 sendv, '3:45:40' sendt from dual)
select sendid, sendv, sendt
from (select sendid,
sendv,
sendt,
row_number() over(partition by sendid order by sendt desc) as rn
from t)
where rn = 1;
select t1.senid,t2.senvalue,t2.time from (select senid,max(time)max_time from table group by senid)t1,table t2
where t1.senid=t2.senid and t1.max_time=t2.time
