Usage of rownum in Oracle

RowNum is the sequential numbering of query results by Oracle, with the first row assigning 1, the second row 2, and so on. RowNum cannot be prefixed with the name of any table.

RowNum this pseudo field can be used to control the number of record rows returned.

For example, table: Student (Student) table, the table structure is:

IdChar(6)--School NumberNameVARCHAR2(Ten)--nameCreate TableStudent (IDChar(6), nameVARCHAR2( -));Insert  intoSaleValues('200001', ' Zhang Yi ');Insert  intoSaleValues('200002', ' Wang er ');Insert  intoSaleValues('200003', ' lie Triple ');Insert  intoSaleValues('200004', ' Zhao Si ');Commit;

1. rownum for queries that are equal to a value:

Query the first student's information, you can use Rownum=1 as a condition, but the second one can not be queried with rownum=2.

Because rownum are all starting from 1, but more than 1 of the natural number in rownum do equal to the judgment is false, so can not find rownum = N (n>1 natural number).

Select  from where rownum=1; (can be used to limit the number of records to be returned, guaranteed to be error-prone, such as: implicit cursors)rownum  ID     NAME   1   200001   Zhang Yi

Select  from where = 2 ; ROWNUM   ID  NAME
Countless data

2. rownum for queries that are larger than a value:

If you want to find records from the second row, when you use Rownum>2, the record is not found because RowNum is a pseudo-column that always starts at 1.
Oracle believes that the condition of rownum> N (natural number of n>1) is still not valid, so the records are not found.

Select  from where > 2 ; ROWNUM   ID   NAME
Countless data

The 2nd record can be obtained through a subquery, but the rownum in the subquery must have an alias, because RowNum is not a column of a table, and if it is not, it is not known whether RowNum is the column of the subquery or the column of the main query.

 select  *  select  rownum no , id,name from  student) where  no< Span style= "COLOR:RGB (128, 128, 128); >>  ;no ID NAME  3  200003   lie triple  4  200004  Zhao Four 

Select *  (selectrownumfromwhere rownum>2; ROWNUM  ID  NAME
Countless data

3. rownum for queries that are less than a certain value:

Query the previous record of the third article, when using Rownum<3 can get two records. RowNum the conditions of rownum<n (natural number of n>1) are considered to be established, so records can be found.

Select  from where < 3 ; ROWNUM    ID      NAME   1     200001    one     2     200002   Wang ER

4. rownum a query for a range of data:

RowNum a query condition that is less than a value is true, RowNum is false for a query condition that is greater than a value, but it can be indirectly converted to true. Then use a subquery.

For example, to query the data (inclusive) of rownum between rows 2nd through 3rd, let's first return records that are less than or equal to 3, and then judge the row of records for the new rownum in the main query. >=2.
However, such operations can affect speed in a large data set.

Select *  from(SelectRowNum No,id,name fromStudentwhereRowNum<=3)whereNo>=2; NO ID NAME2   200002Wang er3   200003Lie triple

5. sort of rownum:

The rownum in Oracle is the sequence number that is generated when fetching data, so it is important to be aware of the rowmun rows of data that you want to specify for the sorted data.

SelectRowNum, Id,name fromStudentOrder  byname; ROWNUM ID NAME3      200003lie triple2      200002Wang er1      200001Zhang Yi4      200004Zhao Four

As you can see, rownum is not the ordinal number generated by the Name column. The system assigns the ordinal number after the first query result, and the ROWID is also assigned sequentially.

In order to solve this problem, subqueries should be used:

SelectRowNum, Id,name from(Select *  fromStudentOrder  byname); ROWNUM ID NAME1    200003lie triple2    200002Wang er3    200001Zhang Yi4    200004Zhao Four
This will be sorted by name, and the correct sequence number (small to large) is marked with rownum.

Personal Summary:

1. RowNum can be used directly for =1 or <.

2. For > or a range of queries: first give rownum alias, using sub-query processing into normal query results, and further processing.

Example Demo:

SELECTZ.*, ROWNUM row_num  from(SELECTA.bookid, A.vote_month fromTbbookclick aWHEREVote_month> 0      ORDER  byA.vote_monthDESC) ZWHERE ROWNUM <=Inintpagesize*Inintpageindex

 OPENOutcurlist for            SELECT T.bookid, T.bookname, T.authorid, T.authorname, T.channelid, T.channelname, t . CategoryID, T.categoryname, T.subcategoryid, T.subcategoryname, T.actionstatus, t . Newchapterid, T.newchaptername, T.newchaptertime, T.newvipchapterid, T.newvipchap Tername, T.newvipchaptertime, T.vipstatus, M.clickrate_month fromTbbook T, (SELECTBookID, Clickrate_month from(SELECTZ.*, ROWNUM row_num from(SELECTA.bookid, A.clickrate_month fromTbbookclick aWHEREClickrate_month> 0                                      ORDER  byA.clickrate_monthDESC) ZWHERE ROWNUM <=Inintpagesize*Inintpageindex)WHERERow_numbetweeninintpagesize*(Inintpageindex- 1) + 1                                          andInintpagesize*Inintpageindex) MWHERET.bookid=M.bookidORDER  byClickrate_monthDESC;

Outside the station read:

Parsing Oracle's RowNum