Instructions and tips for using rownum in Oracle

Oracle is commonly used to rownum, so do some of my knowledge of rownum and the use of skills to record for reference.

I. Description of the RowNum

RowNum is a keyword unique to Oracle.

(1) For base tables, when an insert is recorded, Oracle assigns rownum to each row of records in the order of INSERT, so when you select a base table, the RowNum sort is displayed in the order of the insert records, for example:

Select RowNum as RN, t.* from EMP t;

(2) for subqueries, the order of RowNum is dynamically allocated based on the query order of the subquery, for example:

Select RowNum as T2_rn, t2.* from (select RowNum as T1_rn, t1.* from emp T1 ORDER by t1.sal) T2;

The difference between T1_RN and T2_rn can be seen.

The rownum in T1 is assigned according to the default order of the EMP base table, while the inner sub-loops are sorted according to the Sal field, so the rownum of the T2 is assigned according to the order of the inner subquery's record.

-----------------------------Split Line-------------------------------------

Ii. some tips for using rownum

(1) Use rownum to limit the number of records returned by the query

1. For example, we now only want to see the first record in the EMP table:

SELECT * from EMP where rownum=1;

Limit the rownum to 1 so that only one record can be queried.

2. Now, we want to view the first 2 records in EMP:

SELECT * FROM EMP where rownum<=2;

Limit the rownum to 2, so that the first 2 records can be queried.

3, if we just want to see the second record in the EMP, how to write the statement?

If we write this first:

SELECT * from EMP where rownum=2;

The Where condition is: rownum=2, to see the results of the query:

Found no data detected, why? There is a need for further understanding of Oracle's rownum.

Because rownum is not stored in each table as entity data, but in each select query, according to the default insert order of the base table is dynamically allocated by Oracle, there are 1 only 2, if RowNum does not have 1, then 2 has no meaning, So the query will not have any results. At this point we need to use subqueries and alias names to achieve this requirement:

SELECT * FROM        (select RowNum as RN, t.* from EMP t where rownum<=2) where rn=2;

First through the subquery, take out the first 2 records of the EMP table, and rownum in the subquery is defined as the alias RN, and then in the outer query, using the Where condition to enable rn=2, the second record of the EMP table is queried:

Instructions and tips for using rownum in Oracle