求助 只用SQL 能实现这个查询吗
id time code
1 0703 a
1 0702 a
1 0701 b
1 0630 a
2 0703 a
2 0702 a
2 0701 a
2 0630 b
2 0629 b
2 0628 c
.....
有上述表格，要求查询结果是
id time code
1 0703 a
1 0701 b
1 0630 a
2 0703 a
2 0630 b
2 0628 c
就是order by id, time. 如果是连续code相同的取time最大的那一行，
其他的都不要。能在一个sql里查询出来吗？
[解决办法]
select t.id,t.time.t.code from
(selet id,time,code,row_number() over(partition by id,code order by time desc) rn
from tab_name) t
where t.rn=1;
------解决方案--------------------
with tb as(
select 1 as id,'0703' as time,'a' as code from dual union all
select 1,'0702','a' from dual union all
select 1,'0701','b' from dual union all
select 1,'0630','a' from dual union all
select 2,'0703','a' from dual union all
select 2,'0702','a' from dual union all
select 2,'0701','a' from dual union all
select 2,'0630','b' from dual union all
select 2,'0629','b' from dual union all
select 2,'0628','c' from dual)
select id,time,code from(
select id,time,code,(select min(time) from tb where a.id=id and a.time<time )a from tb a
)a
where nvl((select code from tb where id=a.id and a=time),'$')!=code
