rownum与ORDER BY疑问 rownum不支持order by 排序 先排序再rownum的方法

select pelf.element_link_id,pelf.object_version_number,pelf.effective_start_date
         into   l_element_link_id,l_object_version_number,l_effective_start_date
         from pay_element_links_f pelf
         where pelf.payroll_id=l_payroll_id
         and   pelf.element_type_id=l_element_type_id
         and   nvl(pelf.organization_id,0)=nvl(l_organization_id,0)
       -- and   nvl(pelf.location_id,0)=nvl(l_location_id,0)
      --   and   nvl(pelf.grade_id,0)=nvl(l_grade_id,0)
      --   and   nvl(pelf.position_id,0)=nvl(l_position_id,0)
         and   nvl(pelf.people_group_id,0)=nvl(l_people_group_id,0)
         and   rownum=1 --只取第一条记录
         and   pelf.effective_end_date=to_date(substr('4712-12-31',1,10),'YYYY-MM-DD')
        ORDER BY nvl(pelf.object_version_number,-1) desc;

--此语句是有问题的，rownum 是不支持order by 排序的。因为rownum是在排序之前取值的。

1.oracle手册上说的：“即使select语句中一条简单的order by都可能会搞乱ROWNUM（因为ROWNUM是排序前分配给各行的） ”。

2.在oracle中如果rownum与order by同在，是先rownum，然后再order by   
如 select * from 表名 where rownum <10 order by 字段 
这样会先取rownum小于10的记录，然后再对这10条进行排序

但是，有文章说如果对排序字段建立了索引，则上面的语句就能产生先排序，再rownum的效果。 

但是经过我测试，好像建立索引不行，数据条数的物理存储顺序并没有改变，只有建立主键后，才能实现上述的结果。 

有没有其他解决方法能先排序再rownum ，嵌套的方法不算，如下这样的 
select * from （select * from 表明 order by 字段） where rownum < 10

3.讨论rownum和order by是没有意义的 
rownum是一个伪列，和数据的组织方式相关，它不能唯一标识记录 
如果你没有定义主键或能唯一标识记录的字段的话，唯一标识记录的是rowid，插入一条记录后就为有个唯一的rowid来标识，且rowid不会再改变。 
但是如果你的表改变了表空间的话，rowid也可能会改变

4.1. "在oracle中如果rownum与order by同在，是先rownum，然后再order by " ,这个论述是对的. 

2.可以借助于oracle 的row_number 分析函数来实现zl 的需求. 
SQL > select * from test a; 

ID NAME 
---------- ---------- 
1 lasdjfla 
3 lasdje23 
3 kk 
3 bbb 
2 lasdje23 
2 aaa 

6 rows selected 

SQL > select a.* from test a where rownum <6; 

ID NAME 
---------- ---------- 
1 lasdjfla 
3 lasdje23 
3 kk 
3 bbb 
2 lasdje23 

SQL > select a.* ,row_number() over ( order by id ) rn from test a where rownum <6; 

ID NAME RN 
---------- ---------- ---------- 
1 lasdjfla 1 
2 lasdje23 2 
3 lasdje23 3 
3 kk 4 
3 bbb 5 
SQL >  

 

想要解决和order by的结合使用问题，可以用内嵌视图：inline view
如：
select *

from(

select empno,job,mgr

from emp

order by mgr desc)a

--此a代表的视为内嵌视图：inline view：并不是存储在DB中的命名视图

where rownum<=5;

例子很简单 scott用户下，对emp表的操作

主要通过这个例子来更好的理解关于order by 结合使用rownum的情况

例子如下：

select *

from(

   select empno,job,mgr

from emp

   order by mgr desc)a

--此a代表的视为内嵌视图：inline view：并不是存储在DB中的命名视图

where rownum<=5;

     EMPNO JOB              MGR

---------- --------- ----------

      7839 PRESIDENT

      7370 CLERK           7902

      7597 MANAGER         7839

      7699 MANAGER         7839

      7813 MANAGER         7839

select *

from(

    select empno,job,mgr

   from emp

    order by mgr)

where rownum<=5;

     EMPNO JOB              MGR

---------- --------- ----------

      7788 ANALYST         7566

      7902 ANALYST         7566

      7499 SALESMAN        7698

      7521 SALESMAN        7698

      7844 SALESMAN        7698

======================================================================

以下结果是先返回表的前5条记录，然后按照order by mgr;进行默认的升序排列

======================================================================

select empno,job,mgr

from emp

where rownum<=5

order by mgr;

     EMPNO JOB              MGR

---------- --------- ----------

      7499 SALESMAN        7698

      7521 SALESMAN        7698

      7654 SALESMAN        7698

      7597 MANAGER         7839

      7370 CLERK           7902

========================

原始数据：

select empno,job,mgr

from emp;

EMPNO JOB              MGR

----- --------- ----------

7370 CLERK           7902

7499 SALESMAN        7698

7521 SALESMAN       7698

7597 MANAGER         7839

7654 SALESMAN        7698

7699 MANAGER         7839

7813 MANAGER         7839

7788 ANALYST         7566

7839 PRESIDENT

7844 SALESMAN        7698

7877 CLERK           7788

7900 CLERK           7698

7902 ANALYST         7566

7965 CLERK           7782