我的oracle版本为7.3.4;
表A
ID NAME
1 LSH
4 BDI
3 LSJ
2 KID
5 IKD
我想取出表中按照ID字段排序的前N条记录,结果要求是
ID NAME
1 LSH
2 KID
3 LSJ
于是我这样写select * from (select * from A order by ID) where rownum<4; 但oracle客户端提示:ORA-00907: missing right parenthesis;我估计是我的oracle版本太低了不支持该SQL语句.请问各位还可以怎写SQL语句可以查询出该结果!多谢!
---------------------------------------------------------------
好像是8i才开始支持嵌套里面使用排序
---------------------------------------------------------------
升级吧,到metalink.oracle.com下载patches
---------------------------------------------------------------
略作修改,你的ID不会有负数吧?
create or replace function idno (recordno number)
return number is
myid number;
type ref_cursor is ref cursor;
cursor_getid ref_cursor;
begin
open cursor_getid for
select id from a order by id;
loop
fetch cursor_getid into myid;
exit when cursor_getid%notfound or cursor_getid%rowcount=recordno;
end loop;
close cursor_getid;
if recordno<=0 then
myid:=0;
end if;
return myid;
end;
/