oracle order by 和 rownum<1结果失败问题解决并且 fetch first很好的解决

#在oracle中如下所示

SQL> select max(sequence#) from v$archived_log order by sequence#;

MAX(SEQUENCE#)
--------------
       193

#使用如下字句查询时候

SQL> select sequence# from v$archived_log where rownum<4 order by sequence# desc;

 SEQUENCE#
----------
       140
       139
       138

出现结果和实际情况不符

可以用以下两种办法解决

SQL> select * from (select sequence# from v$archived_log order by sequence# desc) where rownum<4;

 SEQUENCE#
----------
       193
       192
       191

第二种

SQL> select sequence# from (select t.*,row_number() over(order by sequence# desc) rn from v$archived_log t) where rn<4;

 SEQUENCE#
----------
       193
       192
       191

12c之后推出了fetch first 简单实用

SQL> select sequence# from v$archived_log order by sequence# desc fetch first 3 rows only;

 SEQUENCE#
----------
       193
       192
       191

很简单具体其他用法可以查看官网