Since the use of ROWNUMs cannot be implemented for queries, the following is a simple exploitation example: Select Username, Rownum from DBA_USERS WHERE ROWNUM <4 ORDER BY UserName; username rownum -------------- ------------ ------ Outln 3 Sys 1 SYSTEM 2 This can be solved by obtaining records and then sorted:
However, the test can be used directly on Oracle9i to correct this shortcomings when I have issued a new version. SELECT UserName, Rownum from (SELECT UserName from DBA_USERS ORDER BY Username) Where Rownum <4; username rownum ----- -------------------------- Aurora $ Orb $ unauthenticated 1 ctxsys 2 dbsnmp 3 but this is relatively low
If your order by the column of ORDER BY, you can use it to use the list as the master key or index.
Select Username, Rownum from DBA_USERS WHERE ROWNUM <4 Order by UserName;
The method is obtained before the sorting, the first three rows.
One (>) symbol and rownum are used together, there is no result: 1 Select Username, Rownum from 2 (Select UserName from dba_users order by username) 3 * Where rownum> 4 SQL> / no rows selected to display the most The three records of the end cannot be used (>): 1 Select Username, Rownum from 2 (Select User "DBA_USERS ORDER BY Username Desc) 3 * Where Rownum <4 SQL> / UserName Rownum ----------- --------------- ------ Testuser 1 System 2 Sys 3 ------------