Oracle  [RowNum] 是一个伪例，是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配1，第二是2，第三行是3,…...依次类推. 此伪列可以用来限制记录返回的总数据，相当于Sql Server 中的 top 关键字的功能，他不能以任何表名做为关键字。

How ROWNUM Works

ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N, where N is the number of rows in the set ROWNUM is used with. A ROWNUM value is not assigned permanently to a row (this is a common misconception). A row in a table does not have a number; you cannot ask for row 5 from a table—there is no such thing.

Also confusing to many people is when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:

select *
  from t
 where ROWNUM > 1;

Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1. Consider a query with this structure:

select ..., ROWNUM
  from t
 where
 group by
having
 order by ;

Think of it as being processed in this order:

1. The FROM/WHERE clause goes first.

2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.

3. SELECT is applied.

4. GROUP BY is applied.

5. HAVING is applied.

6. ORDER BY is applied.

That is why a query in the following form. is almost certainly an error:

select *
  from emp
 where ROWNUM <= 5
 order by sal desc;

The intention was most likely to get the five highest-paid people—a top-N query. What the query will return is five random records (the first five the query happens to hit), sorted by salary. The procedural pseudocode for this query is as follows:

ROWNUM = 1
for x in
( select * from emp )
loop
    exit when NOT(ROWNUM <= 5)
    OUTPUT record to temp
    ROWNUM = ROWNUM+1
end loop
SORT TEMP

It gets the first five records and then sorts them. A query with WHERE ROWNUM = 5 or WHERE ROWNUM > 5 doesn't make sense. This is because a ROWNUM value is assigned to a row during the predicate evaluation and gets incremented only after a row passes the WHERE clause.

Here is the correct version of this query:

select *
  from 
( select *
    from emp
   order by sal desc )
 where ROWNUM <= 5;

This version will sort EMP by salary descending and then return the first five records it encounters (the top-five records). As you'll see in the top-N discussion coming up shortly, Oracle Database doesn't really sort the entire result set—it is smarter than that—but conceptually that is what takes place.

  上述是其工作原理，摘自：http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

  rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时认为都是false条件，所以无法查到

  rownum = n（n>1的自然数）。

   例如：

SQL> select * from  test;

        ID USERNAME

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

         1 test1

         2 test2

         3 test3

         4 test4

         5 test5

         6 test7

         7 test7

         8 test8

已选择8行

SQL> select * from  test;

        ID USERNAME

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

         1 test1

         2 test2

         3 test3

         4 test4

         5 test5

         6 test7

         7 test7

         8 test8

已选择8行

SQL> select * from  test where rownum=1;

        ID USERNAME
---------- ------------------------------
         1 test1

SQL> select * from  test where rownum>1;

未选定行

SQL> select * from  test where rownum=2;

未选定行

这是因为：

Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1

要想得这样的结果：

SQL> select * from (select  rownum seq ,t.* from test t) where seq>1;

       SEQ         ID USERNAME

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

         2          2 test2

         3          3 test3

         4          4 test4

         5          5 test5

         6          6 test7

         7          7 test7

         8          8 test8

已选择7行。

因此对rownum 的使用，关键是要理解“是oracle系统顺序分配为从查询返回的行的编号”，这样就

可以灵活使用RowNum