你这样是不行的,需要先排序再取rownum,否则rownum的值仍然是排序之前的select * from
(select icp.item_id
from t_insured_change_product icp
where 1=1
and policy_id = 3964916
and change_id = 2471842
order by icp.item_id desc
)
where rownum =1
(select icp.item_id
from t_insured_change_product icp
where 1=1
and policy_id = 3964916
and change_id = 2471842
order by icp.item_id desc
)
where rownum =1
http://www.etake.com.cn/bbs1/printpage.asp?BoardID=5&ID=35
依此类推,这个伪字段可以用于限制查询返回的总行数。
2、rownum不能以任何基表的名称作为前缀。
(select ...
order by ... ) t
where t.rownum =1
select a.title,(a.retail-a.cost)/a.cost*100 as profit_rate ,rownum from(
select isbn,title,(retail-cost)/cost*100 ,retail,cost,sum(quantity)
from books natural join orderitems
group by isbn,title,(retail-cost)/cost*100,retail,cost
order by sum(quantity) desc) a
where rownum<=3;
看看