select ut_id,start_date,tky_dte_id from
(select ut_id,start_date,tky_dte_id,(rank() over (partition by ut_id order by ut_id,tky_dte_id desc)) rid from table1)
where rid=1;
(select ut_id,start_date,tky_dte_id,(rank() over (partition by ut_id order by ut_id,tky_dte_id desc)) rid from table1)
where rid=1;
(select ut_id,max(tky_dte_id) from table1
where hn_id = 01,srt_id = 1 group by ut_id) t2
where t1.ut_id=t2.ut_id;
from table1
where hn_id = 01,srt_id = 1
group by ut_id
select ut_id,start_date,max(tky_dte_id) over(partition by ut_id,start_date) rk
from table1
where hn_id = 01,srt_id = 1;
我想group by ut_id的值, 但在返回的时候 又想得到 ut_id,start_date
(select ut_id,max(tky_dte_id) as aa from table1
where hn_id = 01,srt_id = 1 group by ut_id) t2
where t1.ut_id=t2.ut_id and t1.tky_dte_id=t2.aa
这样才行,我测试过了
SQL> select a.id,b.aa,a.id2 from ss a,(select id,max(id1) as aa from ss
2 group by id) b where a.id=b.id and a.id1=b.aa
3 ; ID AA ID2
---------- ---------- ----------
1 2 1
2 3 5
或者用自连接select ut_id,start_date,tky_dte_id from
(select * from table1 where hn_id = 01 and srt_id = 1)o where not exists(
select * from (select * from table1 where hn_id = 01 and srt_id = 1) i where i.ut_id=o.ut_id and i.tky_dte_id >o.tky_dte_id)
有没有更好的写法呢