一个有关oracle分页sql的问题，在线等候。

[zhaolh2002]

1：问题：
Connected to Oracle8 Release 8.0.5.0.0
Connected as system
SQL> select * from ( select t.message_id,t.modify_time,rownum as no from message_information t ) where no >30 ;

MESSAGE_ID MODIFY_TIME NO
--------------------------------------- ------------------------- ----------
1565 2004-02-02 10:32 31
1566 2004-01-31 10:31 32
1573 2004-01-31 10:39 33
1575 2004-01-31 10:41 34
1574 2004-01-31 10:40 35
1576 2004-01-31 10:41 36
1577 2004-01-31 10:42 37
1578 2004-01-31 10:42 38
1571 2004-01-31 10:38 39
1570 2004-01-31 10:38 40
1572 2004-01-31 10:39 41
1568 2004-01-31 10:38 42
1567 2004-01-31 10:31 43
1569 2004-01-31 10:38 44

14 rows selected
SQL> select * from ( select t.message_id,t.modify_time,rownum as no from message_information t order by t.modify_time ) where no >30 ;

select * from ( select t.message_id,t.modify_time,rownum as no from message_information t order by t.modify_time ) where no >30

ORA-00907: 缺少右括号
2：环境
表message_information的modify_time字段为VARCHAR2(25)

[bzszp]

不能order by，去掉

[zhaolh2002]

功能上需要排序，不能去掉。

[bzszp]

刚才说错了，sorry

select rownum no,t.* from (select t.message_id,t.modify_time from message_information t order by t.modify_time )t where no >30

[zlqs]

是不是8I不支持？
我在9。0。0。1上测试通过


SQL> SELECT ID,AID,ROWNUM FROM TEST_DG ORDER BY ID;

ID AID ROWNUM
--------------------------------------------- ---------- ----------
1(AID) 1 5
1(CID) 2 6
2(AID) 2 1
2(CID) 3 2
3(AID) 3 7
3(CID) 1 8
4(AID) 4 3
4(CID) 4 4

8 rows selected

SQL> SELECT * FROM (SELECT ID,AID,ROWNUM FROM TEST_DG ORDER BY ID ) WHERE AID>2;

ID AID ROWNUM
--------------------------------------------- ---------- ----------
2(CID) 3 2
3(AID) 3 7
4(AID) 4 3
4(CID) 4 4

[lijin75]

oracle 8i以下不支持子查询排序。

[zhaolh2002]

我用的是8.0.5.

[lijin75]

那就没办法了，你是想查询排在前N位或后N位的记录吗，根据实际需要可以用别的查询语句实现。