oracle 7 高手入 排序

SELECT * FROM (SELECT *  FROM TABLE ORDER BY ID ASC ) WHERE ROWNUM <= 10

以上既sql 係版本係7 唔work.

求指教有咩方法可以係7上面做到以上效果 排序同拎頭十條.

Thank
附件: 您需要登錄才可以下載或查看附件。沒有帳號?註冊

陳年舊嘢,你試下呢個,唔知ora7有冇support:
select level as rnum
from dual
connect by level <= 10;

TOP

陳年舊嘢,你試下呢個,唔知ora7有冇support:
select level as rnum
from dual
connect by level  ...
望月小妖 發表於 2015-3-11 21:46



    connect by 好似 8 定 9 先有

TOP

點解唔係最後先做order?

TOP

ORACLE 7冇ROWNUM你玩呀?
http://docs.oracle.com/cd/A57673_01/DOC/server/doc/SQL73/ch2.htm

出咩ERROR?

TOP

有rownum 不過唔可以同order by 包住

TOP

本帖最後由 tuyylihk 於 2015-3-12 00:50 編輯
有rownum 不過唔可以同order by 包住
小煩仔 發表於 2015-3-12 00:35

睇唔出有咩特別問題...手邊冇DB可以試要聽日先

SELECT ROWNUM, a.* from a order by xyz
呢句得唔得

得架話應該唔駛教...唔得架話我再諗諗....

TOP

SELECT * FROM (
SELECT ROWNUM as rn, *  FROM TABLE ORDER BY ID ASC
) WHERE rn <= 10

咁得唔得呢 ?

TOP

本帖最後由 董事長 於 2015-3-12 09:55 編輯

你個ID係 text黎
ORDER BY to_number(ID)

p.s. 假設ID全部係數字先咁做

TOP

terrykk's solution is correct.

However, if the table is big and the sort column is not indexed, it will be rather slow without analytic SQL function.

In such case, you'd better do the Top-N part in your procedural program such by PL/SQL, Java, etc. by retrieving the first N rows only:
  1. declare
  2.     cursor  c is
  3.     select  *
  4.     from    t
  5.     order   by id;
  6.    
  7.     l_rec c%rowtype;
  8. begin
  9.     open c;
  10.     for i in 1 .. 10
  11.     loop
  12.         fetch c into l_rec;
  13.         exit when c%notfound;
  14.         // do something with l_rec
  15.     end loop;
  16.     close c;
  17. end;
  18. /
複製代碼

TOP