ORACLE取日期最近的记录
件号名称供应商数量时间 使用者批号备注
1aaa202013/5/1 0:00e23440
1aaa302013/5/2 0:00e12341
2bbb852013/5/3 0:00r23410
2bbb112013/5/4 0:00j32442
3ccc32013/5/5 0:00r34232
3ccc302013/5/6 0:00g12233
4ddd482013/5/7 0:00j21341
如上图所示，根据件号、供应商汇总数量，然后时间、使用者、批号、备注显示为时间最近的记录。
结果如下图所示：
件号名称供应商数量时间使用者批号备注
1aaa302013/5/2 0:00e12341
2bbb112013/5/4 0:00j32442
3ccc302013/5/6 0:00g12233
4ddd482013/5/7 0:00j21341
请高手指点，用SQL语句实现。
[解决办法]
select * from
(select t.*,row_number() over(partition by 件号 order by 时间 desc ) rn from t) t
where t.rn=1
[解决办法]
with t1 as
(
select 1 c1,'a' c2,'aa' c3,20 c4,'2013-05-01' c5,2222 c6 from dual union all
select 1 c1,'a' c2,'aa' c3,30 c4,'2013-05-02' c5,1111 c6 from dual union all
select 2 c1,'b' c2,'bb' c3,85 c4,'2013-05-03' c5,3333 c6 from dual union all
select 2 c1,'b' c2,'bb' c3,11 c4,'2013-05-04' c5,4444 c6 from dual
)
select c1,c2,c3,c4,c5,c6
from
(
select c1,c2,c3,c4,c5,c6,row_number() over(partition by c1 order by c5 desc) rn
from t1
)
where rn = 1
c1 c2 c3 c4 c5 c6
----------------------------
11aaa302013-05-021111
22bbb112013-05-044444
