rownumber的问题？

Question

rownum 是在查询出结果并排序完成之后给出的附加行号
select * from t where rownum = 1 and id < 3 order by id desc 应该得到2
select * from t where rownum = 1 order by id desc    4

Answer 1

既然已经限定了id<3并且按照id desc排序，自然不能取到id=4的记录。

Answer 2

很显然，where后面跟的 id<3 条件已经筛选并过滤了 id=4 的记录。

Answer 3

chd2001(天蝎降临--千分散尽还复来):
很显示不是你所想：select * from t where rownum = 1 and id < 3 order by id desc 的结果是1,不是2,我想得到2

Answer 4

select * from t where rownum = 1 where id < 3 order by id desc
得到的结果为1错，应该是2

Answer 5

试了下  select * from t where rownum = 1 where id < 3 order by id desc
的结果是1  很奇怪

Answer 6

oracle是先给rownum 后order by

Answer 7

mingxuan3000(铭轩)：
oracle是先给rownum 后order by，如果是这样的话，为何
select * from t where rownum = 1 order by id desc的结果为4,却不为1？我的环境是Oracle817

Answer 8

SORRY 
我在我这系统的里用这个方法查询，等得到我在1楼给出的结果
但是我自己重新建了一个表之后，得到的结果就完全是按照ROWID 排的
正在寻找区别。
--
SQL> select * from test1 where rownum=1 order by f1 desc;F1         F2         F3
---------- ---------- ----------
1          aa         qq为什么没有和你们验证的那样=4呢 

Answer 9

select rownum,t.*, t.rowid from info t where  rownum=1  order by ino desc
的结果是1

Answer 10

oracle是先给rounum 然后order by的，而刚开始给出结果的时候，是按索引里的顺序，如果有索引的话。如果你重建了表，肯定结果有不同，按理说，重建索引就会有不一样~

Answer 11

如果你想取排序之后的最大值，有一种方法是，select * from (select * from ....order by ...) where rownum=1;

Answer 12

哦，我搞错了
select * from t where rownum = 1 where id < 3 order by id desc
得到的结果为1
羞愧的离去……

Answer 13

如果有rownum条件的话,Oracle会先查出一条指定条数的记录.至于查出来的是哪些记录,应该与物理上的存储顺序有关,而与记录的内容没有直接的关系.查出指定条数的记录之后再对结果进行排序.

Answer 14

打错字,更正一下:
如果有rownum条件的话,Oracle会先查出指定条数的记录.至于查出来的是哪些记录,应该与物理上的存储顺序有关,而与记录的内容没有直接的关系.查出指定条数的记录之后再对结果进行排序.

Answer 15

sql语句是按照先后顺序执行，首先按照where 条件检索出数据集 然后在排序，rownum=1只能取出一条数据，是按照数据库存储的rowid进行检索的

Answer 16

注意：rownum只能用符号(<、<=、!=)。 
            select * from tablename where rownum != 10;返回的是前９条记录。 
            不能用：>,>=,=,Between...and。由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件 只能用
select *
from table_name
where rownum < N
minus
select *
from table_name
where rownum < M就可以选中 M 与 N之间的。rownum是伪列，代表已经查询到的记录的行序号。还未查到的记录是没有rownum的。
因此ROWNUM>=1 AND ROWNUM<=10是可以查到前10条记录但是不能证明
ROWNUM>=500 AND ROWNUM<=600可以查处500～600的501条记录。
ROWNUM>=1 AND ROWNUM<=10在逻辑上相当于ROWNUM<=10
正确的方法是：
select * from (select Z_YXJK_DMDH.*, rownum as rid from Z_YXJK_DMDH where rownum<=600) where rid > 500
或者
select * from Z_YXJK_DMDH where rownum<=600
minus
select * from Z_YXJK_DMDH where rownum<=500

Answer 17

我以前遇到过rownum 与order by同时在一层使用不正常的显现。
后来就改成分层的了。理论上我也不清楚它们的执行优先级别，我猜想：伪列与order by都用到类似堆栈的东东，
那样就与写的位置有关了。to:libin_ftsafe(子陌红尘：当libin告别ftsafe) 
你的回答好像不到位。请高人说明具体的理论根据吧！

Answer 18

rownum是伪列,是在当前结果集出来后才加上去的。所有说，同层时候,order by比rownum先执行。

Answer 19

我想应该是rownum比order by 早，
where -> rownum -> order by也就是说，在应用order by 前得到记录可能是随机的，
也可能受存储情况、索引方式和优化策略影响

Answer 20

select * from t where t.rowid = (
  select MAX( b.rowid) KEEP (DENSE_RANK LAST ORDER BY id ) "Best"
  FROM t b where b.id <3
)

Answer 21

我在另一网站发的帖子：
test1中有顺序添加的4条记录 1\2\3\4
--
SQL> select * from test1 where rownum<2 order by f1;
F1         F2         F3
---------- ---------- ----------
1          aa         33
SQL> select * from test1 where rownum=1 order by f1 desc;
F1         F2         F3
---------- ---------- ----------
1          aa         qq按照这个结果，应该和大部分资料所讲述的一样，rownum在ORACLE查询出结果后、排序之前生成
但是我在另一个表（某系统中一个表）：却得到了不一样的结果
SQL> select rid from t_fix_communication  where rownum<2 and rid<'1001' order by rid;
RID
------------
1000
SQL> select rid from t_fix_communication  where rownum<2 and rid<'1001' order by rid desc;
RID
------------
100099（说明：这个RID是主键+索引列）于是，我在表test1上也添加同类型(users)索引，查询结果还是没发生改变
请问这是什么原因，跟建表时的什么东东有关系？---一个比好有谱的解答：
rid上走索引有关，oracle最常见的表是关系堆表，动态的看，行无先后顺序。所以这里
select /*+full (a) */ rid from t_fix_communication  a where ... 
(并且此表在你测试期间没有人修改,此时你能看到跟你test一样的结果 用asc跟desc排序结果一样）
但是如果 and rid<'1001'此条件oracle使用索引范围扫描的话结果就不一样了，他返回的结果集顺序是与索引读取顺序有关。想知道oracle先返回哪些行，你可以改变rownum<10，然后分别使用全表full扫描，在使用索引范围扫描，看看结果对比一下。
要知道rownum<2只是取得到的结果集的第一行，如上两种返回的结果集顺序不一样，就导致你所说的情况的。
请执行如下查看结果：
select a.rid from t_fix_communication a
  where rownum<10 
  and a.rid<'1001' 
  order by a.rid desc;
select a.rid from t_fix_communication a
  where rownum<10 
  and a.rid<'1001' 
  order by a.rid ;
select /*full (a)*/a.rid from t_fix_communication a
  where rownum<10 
  and a.rid<'1001' 
  order by a.rid desc;
select /*full (a)*/ a.rid from t_fix_communication a
  where rownum<10 
  and a.rid<'1001' 
  order by a.rid ;

Answer 22

楼主的这个问题主要是由于rowid的顺序分配引起的.rowid:只是记录在物力磁盘上存储的唯一物力地址
先对rowid有个感官认识：SQL> select ROWID from Bruce_test where rownum<2; ROWID 
------------------ ---------- 
AAABnlAAFAAAAAPAAA ------------------------------------------------------------
下面来争对楼主的问题做一个测试就明白问题的所在
造成楼主困惑的问题的原因: 
就是id=4的记录在id=1的记录之前插入的,
即id=4的记录rowid < id=1的记录rowid--for example:
1.现测试id=1的记录在id=4的记录前先插入的情况
QL> create table test_tab (id number);Table createdSQL> insert into test_tab values (1);1 row insertedSQL> insert into test_tab values (2);1 row insertedSQL> insert into test_tab values (3);1 row insertedSQL> insert into test_tab values (4);1 row insertedSQL> commit;Commit completeSQL> select * from test_tab;        ID
----------
         1
         2
         3
         4
----楼主的测试语句
SQL> select * from test_tab where rownum = 1 and id < 3 order by id desc;        ID
----------
         1SQL> select * from test_tab where rownum = 1 order by id desc;        ID
----------
         1---可以发现在这种情况下能满组楼主的要求
---下面我们来看看ROWID的情况:
SQL> select rowid,id from test_tab where rownum = 1 and id < 3 order by id desc;ROWID                      ID
------------------ ----------
AAAOH+AALAAALr8AAA          1SQL> select rowid,id from test_tab where rownum = 1 order by id desc;ROWID                      ID
------------------ ----------
AAAOH+AALAAALr8AAA          1SQL> select rowid,id from test_tab;ROWID                      ID
------------------ ----------
AAAOH+AALAAALr8AAA          1       --ID=1的记录的ROWID最小表明是最早生成的数据
AAAOH+AALAAALr8AAB          2
AAAOH+AALAAALr8AAC          3
AAAOH+AALAAALr8AAD          4       --ID=4的记录的ROWID最大表明是最晚生成的数据--从中可以看出实际取的是MIN(ROWID)=AAAOH+AALAAALr8AAA的记录2.再测试id=4的记录在id=1的记录前先插入的情况SQL> drop table test_tab;Table droppedSQL> create table test_tab (id number);Table createdSQL>  insert into test_tab values (4);1 row insertedSQL>  insert into test_tab values (1);1 row insertedSQL>  insert into test_tab values (2);1 row insertedSQL>  insert into test_tab values (3);1 row inserted--楼主的测试语句
SQL> select * from test_tab where rownum = 1 and id < 3 order by id desc;        ID
----------
         1SQL> select * from test_tab where rownum = 1 order by id desc;        ID
----------
         4
--可以看见这边就出现楼主所说的问题了
--在来看ROWID 的情况:
SQL> select rowid,id from test_tab where rownum = 1 and id < 3 order by id desc;ROWID                      ID
------------------ ----------
AAAOH/AALAAALr8AAB          1SQL>  select rowid,id from test_tab where rownum = 1 order by id desc;ROWID                      ID
------------------ ----------
AAAOH/AALAAALr8AAA          4SQL> select rowid,id from test_tab;ROWID                      ID
------------------ ----------
AAAOH/AALAAALr8AAA          4  --ID=4的记录的ROWID最小表明是最早生成的数据
AAAOH/AALAAALr8AAB          1
AAAOH/AALAAALr8AAC          2
AAAOH/AALAAALr8AAD          3  --ID=1的记录的ROWID最大表明是最晚生成的数据
----======================================================================
综上所述:
    决定rownum限定的排序查询结果的另外一个重要原因是rowid.要达到楼主的查询可以使用子查询来做
如下:
SQL> select * from (select * from test_tab order by id desc) where rownum = 1;        ID
----------
         4

Answer 23

你的实验我理解，但为何我这会是这样？我的试验（Oracle817,Windows平台）
create table TEST1
(
  ID NUMBER not null, //id已设置为主键
  F  VARCHAR2(50)
)从小到大增加四条记录（从rowid可以看出）：
select t.*,rowid from test1 t;
-----------------
ID F ROWID
1 AAAGLeAAIAAACO2AAA
2 AAAGLeAAIAAACO2AAB
3 AAAGLeAAIAAACO2AAC
4 AAAGLeAAIAAACO2AAD
select * from test1 where rownum =1 order by id desc
（这条查询为何不是id为1的行？）
--------------------------------
ID F ROWID
4 AAAGLeAAIAAACO2AAD
select t.*, rowid from test1 t where rownum =1 and id<3 order by id desc
--------------------------------
ID F ROWID
1 AAAGLeAAIAAACO2AAA

Answer 24

突然觉得Oracle的rownum有点诡异，不像sql server的top最后执行，即在where,order之后，再取top

Answer 25

刚才 测试出来一些有趣的问题:)
-------
ifsapp@YKKAP>select * from test_tab;        ID
----------
         1
         2
         3
         4ifsapp@YKKAP>select * from test_tab where rownum = 1 order by id desc;        ID
----------
         1ifsapp@YKKAP>delete from test_tab where id = 4;已删除 1 行。ifsapp@YKKAP>insert into test_tab values (4);已创建 1 行。
--不COMMIT数据现查询就能得到楼主的那种情况
ifsapp@YKKAP>select * from test_tab where rownum = 1 order by id desc;        ID
----------
         4ifsapp@YKKAP>commit;提交完成。
--现在再COMMIT查询结果也不变
ifsapp@YKKAP>select * from test_tab where rownum = 1 order by id desc;        ID
----------
         4ifsapp@YKKAP>delete from test_tab where id = 4;已删除 1 行。ifsapp@YKKAP>insert into test_tab values (4);已创建 1 行。ifsapp@YKKAP>commit;提交完成。--但是提交后在查询结果就不一样了
ifsapp@YKKAP>select * from test_tab where rownum = 1 order by id desc;        ID
----------
         1发现COMMIT与不COMMIT的情况得到的情况竟然是不一样的,这个已经在我的所知范围之外,大家一起来讨论下

Answer 26

1.其实在楼主的这个测试里面 order by id desc;是没有意思的语句 
where rownum = 1 直接就把缺省的第一条记录给选择了出来,order by id desc只是对唯一的一条记录进行排序,由下面的测试可以得出
ifsapp@YKKAP>select * from test_tab where rownum = 1 and id < 3 ;        ID
----------
         1ifsapp@YKKAP>select * from test_tab where rownum = 1;        ID
----------
         4ifsapp@YKKAP>select * from test_tab where rownum = 1 and id < 3 order by id asc;        ID
----------
         1ifsapp@YKKAP>select * from test_tab where rownum = 1 order by id asc;        ID
----------
         4
2.我其实没有办法得到完全和楼主一样的情况,在刚才所说的COMMIT之前与之后的查询中我发现ROWID的改变的,如下:
在不同的删除的时候可能会有不同的ROWID:
ifsapp@YKKAP>delete from test_tab where id = 4;已删除 1 行。ifsapp@YKKAP>insert into test_tab values (4);已创建 1 行。ifsapp@YKKAP>select rowid,id from test_tab;ROWID                      ID
------------------ ----------
AAAOIGAALAAALr+AAB          1
AAAOIGAALAAALr+AAC          2
AAAOIGAALAAALr+AAD          3
AAAOIGAALAAALr+AAE          4ifsapp@YKKAP>commit;提交完成。ifsapp@YKKAP>select * from test_tab where rownum = 1 order by id desc;        ID
----------
         1ifsapp@YKKAP>delete from test_tab where id = 4;已删除 1 行。ifsapp@YKKAP>insert into test_tab values (4);已创建 1 行。ifsapp@YKKAP>commit;提交完成。ifsapp@YKKAP>select * from test_tab where rownum = 1 order by id desc;        ID
----------
         4ifsapp@YKKAP>select rowid,id from test_tab;ROWID                      ID
------------------ ----------
AAAOIGAALAAALr+AAA          4
AAAOIGAALAAALr+AAB          1
AAAOIGAALAAALr+AAC          2
AAAOIGAALAAALr+AAD          3在不同的删除的时候可能会有不同的ROWID:原来ROWID也是随即分配的可前可后的连续地址??
注:我无法再现楼主你的那种情况:)我这边的确实是以ROWID排序来默认一个行数据

Answer 27

baojianjun(包子) ：
查询结果不是我手写的，是执行后copy出来的，这一点保证。