select * from ( select rownum rn,* from tabName order by date desc ) where rn = 2
select * from ( select t.*, rownum row_number from table_name order by date_column desc) where row_number=2
select * from ( select rownum rnum,t.* from ( select * from tabName order by date desc ) t where rnum <= 2 ) tt where tt.rnum>1;
or: select * from ( select t.*,row_number() over(order by coldate desc) rnum from tbname ) where rnum=2;
select * from ( select rownum rn,* from tabName order by date desc ) where rn = 2我在ORCAL中运行,为什么提示错误? 主要是rn = 2 不对.
上面的句子语法不对。select * from ( select rownum rn,tabname.* from tabName order by date desc ) where rn = 2; 这样可以运行,但是结果也不正确。
SELECT t2.* FROM (SELECT t.* FROM ( select t.* from zbttb_m70m t ORDER BY keijo_ymd DESC) t WHERE ROWNUM <=2) t2WHERE ROWNUM <= 1;
oracle中有数据行概念。rownum代表了行号。因此要取倒数第二条,需要先将数据倒序,然后再读取第二行记录。select * from tablename order by fieldname_date desc where rownum=2即可。
楼上说的是取出的数据集有行(rownum)的概念,而不是数据库有行的概念
举个例子:(取工资第2高的员工信息) select r,e.* from (select rownum r,last_name,salary from ( select last_name,salary from employees order by nvl(salary,0) desc)) e where r=2;
select *
from
(
select rownum rn,* from tabName order by date desc
)
where rn = 2
select t.*, rownum row_number from table_name order by date_column desc)
where row_number=2
select rownum rnum,t.*
from
(
select * from tabName order by date desc
) t
where rnum <= 2
) tt
where tt.rnum>1;
select * from (
select t.*,row_number() over(order by coldate desc) rnum from tbname
) where rnum=2;
from
(
select rownum rn,* from tabName order by date desc
)
where rn = 2我在ORCAL中运行,为什么提示错误? 主要是rn = 2 不对.
from
(
select rownum rn,tabname.* from tabName order by date desc
)
where rn = 2;
这样可以运行,但是结果也不正确。
select t.* from zbttb_m70m t ORDER BY keijo_ymd DESC) t
WHERE ROWNUM <=2) t2WHERE ROWNUM <= 1;
select r,e.* from (select rownum r,last_name,salary from (
select last_name,salary from employees order by nvl(salary,0) desc)) e
where r=2;