select jobid,effdate,sal from (select jobid,effdate,sal,row_number() over (partition by jobid order by effdate desc) rk from tab) where rk = 1
select jobid,effdate,sal from(select t.*,row_number() over(partition by jobid order by effdate desc) rn from table_name t) where rn = 1;
select jobid, max(effdate), sal from tab_name group by jobid, sal;
select jobid, max(effdate), sal from tab_name group by jobid, sal; 这个好像不可以把,会把所有的记录都显示出来的select jobid,effdate,sal from ( select t.*,row_number() over(partition by jobid order by effdate desc) seq from table_name t ) where seq = 1;
select jobid, max(effdate), sal from tab_name group by jobid, sal; 这个语句可以,我测试过了
不好意思,没有经过认真的思考,那个语句是不好用的。SQL> select * from ddd; COLA COLB COLC ---------- -------------- ---------- 111 01-1月 -05 1000 111 01-5月 -05 2000 222 01-2月 -05 1300 222 03-5月 -05 2300已用时间: 00: 00: 00.00 SQL> select cola,colb,colc from(select t.*,row_number() over(partition by cola order by colb desc) rn from ddd t) where rn = 1; COLA COLB COLC ---------- -------------- ---------- 111 01-5月 -05 2000 222 03-5月 -05 2300已用时间: 00: 00: 00.01
如果只是时间最近:select jobid, max(effdate), sal from tab_name group by jobid;
(select jobid,effdate,sal,row_number() over (partition by jobid order by effdate desc) rk from tab)
where rk = 1
这个好像不可以把,会把所有的记录都显示出来的select jobid,effdate,sal from
(
select t.*,row_number() over(partition by jobid order by effdate desc) seq from table_name t
)
where seq = 1;
这个语句可以,我测试过了
---------- -------------- ----------
111 01-1月 -05 1000
111 01-5月 -05 2000
222 01-2月 -05 1300
222 03-5月 -05 2300已用时间: 00: 00: 00.00
SQL> select cola,colb,colc from(select t.*,row_number() over(partition by cola order by colb desc) rn from ddd t) where rn =
1; COLA COLB COLC
---------- -------------- ----------
111 01-5月 -05 2000
222 03-5月 -05 2300已用时间: 00: 00: 00.01
看来我的sql还是很烂啊!
大虾们能不能推荐一本好的学习sql的书啊?
最好能在网上免费下载的。
多谢啊!