Oracle rownum Usage Detailed ____oracle

Request Process:

1 Oracle executes your query.

2 Oracle fetches the "I" and calls it row number 1.

3 Have We gotten past row number meets the criteria? If no, then Oracle discards the row, if yes, then Oracle return the row.

4 Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).

5 Go to step 3.

For RowNum, it is an Oracle system sequence that is assigned the number of rows returned from the query, the first row is assigned 1, the second row is 2, and so on, this pseudo field can be used to limit the number of rows returned by the query, and rownum cannot be prefixed with the name of any table.

(1) rownum for query conditions that are equal to a value
if you want to find information about the first student in the student table, you can use Rownum=1 as a condition. But to find the second student in the student table, the data is not available using the rownum=2 results. because the rownum are all starting from 1, but 1 of the natural number in the rownum is equal to the judgment is considered false conditions, so can not find rownum = N (n>1 natural number).
Sql> Select Rownum,id,name from student where rownum=1 (you can use the limit to return the number of record bars to ensure no errors, such as implicit cursors)
Sql> Select RowNum, Id,name from student where rownum =2; 
    rownum id     name
---------- ------ ---------------------------------------------------

(2) rownum for query conditions that are greater than a value
   If you want to find records that are recorded from the second row, when you use ROWNUM>2, you cannot find the record. Because RowNum is a pseudo column that always starts with 1, Oracle thinks rownum> n (the natural number of n>1) is still not valid, so no records are found.

finds records after the second row to resolve using the following subquery method . Note the rownum in the subquery must have an alias, otherwise the record will not be detected, this is because rownum is not a column of a table, and if it is not an alias, it is not possible to know whether RowNum is the column of the subquery or the column of the main query .
Sql>select * FROM (select RowNum No, id,name from student) where no>2;
        NO id     NAME
---------------------------- ---------------------------------------
         3 200003 Lie triple systems
          4 200004 Zhao four

(3) RowNum the criteria for a query that is less than a value
rownum is considered valid for rownum<n (the n>1 natural number), so records can be found.
Sql> Select Rownum,id,name from student where RowNum <3;
    rownum id     NAME
------------------------------------------------ -------------------
        1 200001 sheets a
         2 200002 King two

To query rownum data in an interval, you must use a subquery. For example, to query rownum between the second and third rows of data, including the second and third rows of data, we can only write the following statement, let it return a row of records less than or equal to three, and then in the main query to determine the new rownum is greater than or equal to two of the record row. But such operations can affect speed in large data sets.
Sql> SELECT * FROM (select rownum no, id,name from student where rownum<=3) where no >=2;
NO ID NAME
---------- ------ ---------------------------------------------------
2 200002 King Two
3 200003 Lie triple systems

(4) RowNum and sorting
The rownum in Oracle is the number that is produced when the data is fetched,SoThe Rowmun row data you want to specify for the specified sort of data must be noted.
Sql> Select RowNum, id,name from student order by name;
RowNum ID NAME
---------- ------ ---------------------------------------------------
3 200003 Lie triple systems
2 200002 King Two
1 200001 Sheets A
4 200004 Zhao Si
can see thatrownum is not a sequence number that is generated by the name column. The system is the number that records are given in the order in which they were inserted,rowID are also assigned in order. To solve this problem,subqueries must be used;
Sql> Select RowNum, Id,name from (SELECT * to student order by name);
RowNum ID NAME
---------- ------ ---------------------------------------------------
1 200003 Lie triple systems
2 200002 King Two
3 200001 Sheets A
4 200004 Zhao Si
This is sorted by name, and the correct ordinal number is marked with rownum (small to large)
The author has a millions record in the work of the table, in the JSP page to the table to be paged out, then consider using rownum to do, the following is the specific method (20 per page):
"SELECT * from TabName where rownum<20 order by name" but found that Oracle was not able to do what it wanted, but insteadtake 20 records first and then order by, after consulting Oracle, said RowNum indeed so, want to use words,You can only use subqueries to achieve the first sort, after RowNum, the method is as follows:
"SELECT * FROM (SELECT * to TabName order by name) where Rownum<20", but in this way, the efficiency is much lower.
After the test by the author, justby Adding a primary key or index to the field on the order by, Oracle first sorts by that field,and then rownum; The method does not change: "SELECT * from TabName where rownum<20 order by name"

Gets the nth row in a column

Select column_name from
(select Table_name.*,dense_rank () over (Order BY column DESC) rank from table_name)
where rank = &N;
If you want to return the first 5 records:

SELECT * FROM tablename where rownum<6; (or rownum <= 5 or rownum!= 6)
If you want to return the 第5-9条 record:

SELECT * FROM tablename
where ...
and rownum<10
Minus
SELECT * FROM tablename
where ...
and rownum<5
Order BY name
After selecting the results, sort the results by name. (Select and reorder first)

Note: Only the above symbols (<, <=,!=) can be used.

SELECT * FROM tablename where rownum!= 10; The first 9 records are returned.
Can't use: >,>=,=,between...and. Because RowNum is a pseudo column that always starts with 1, Oracle does not consider this condition to be tenable.

In addition, this method is faster:

SELECT * FROM (
Select RowNum r,a from yourtable
where RowNum <= 20
Order by name )
where r > 10
So take out the 第11-20条 record! ( Select first and then sort and then select)

To sort and then select the Select nesting : The inner layer sorting outer selection.
RowNum is generated with the result set, once generated, it will not change, at the same time, the resulting results are accumulator in turn, No 1 will never have 2!
A rownum is a pseudo column that is produced during a query collection, and if a rownum condition exists in the Where condition:

1: If the decision condition is constant, then:
can only rownum = 1, <= is greater than 1 of the natural number, = more than 1 of the number is not the result of more than a number is not a result of
That is, when a rownum does not meet the condition, the query ends this is stop key (one is not satisfied, the system will filter out the record, the next record is the rownum or this, so there is no longer satisfied with the record, this is stop key) ；

2: If the decision value is not a constant, then:

If the condition is = Var, only when the Var is 1 to meet the conditions, this time does not exist stop key, must be full scan, each meet the other where conditions of the data to determine, select a row to select the rownum=2 line ...

The following excerpt from the China It Lab

1. Implement select Top N in Oracle

Because Oracle does not support select top statements, it is often used in Oracle to implement a SELECT top N query with a combination of order by and RowNum.

Simply put, the implementation method looks like this:

SELECT Column Name 1 ... Column name N from

(SELECT column name 1 ... Column name n from table name order BY column name 1 ... Column name N)

WHERE rownum <= N (number of extracted records)

ORDER by RowNum ASC

Here's a quick example to illustrate.

Customer table Customer (Id,name) has the following data:

ID NAME

-A

Second

Third

Forth

Fifth

Modified Sixth

Modified Seventh

Eighth

Ninth

Ten Tenth

One last

The SQL statements for the first three customers by name letter are as follows:

SELECT * FROM

(SELECT * from CUSTOMER ORDER by NAME)

WHERE rownum <= 3

ORDER BY RowNum ASC

The output results are:

ID NAME

Eighth

Fifth

-A

2. Extract m (m <= N) records in top N records

RowNum is a hidden fragment of the data number in the record table, so you can extract the record's rownum at the same time as the top n record, and then extract the record number m from the N records, even if we want to get the result.

From the above analysis you can easily get the following SQL statement.

SELECT Column Name 1 ... Column name N from

     (

SELECT rownum recno, column name 1 ... Column name Nfrom

(SELECT column name 1 ... Column name n from table name order BY column name 1 ... Column name N)

WHERE rownum <= N (number of extracted records)

ORDER by RowNum ASC

     )

WHERE recno = m (M <= N)

Based on the data of the same table above, the SQL statement that gets the information of the second customer sorted by the letter of name should read as follows:

SELECT ID, NAME from

(

SELECT rownum recno, ID, NAME from

(SELECT * from CUSTOMER ORDER by NAME)

WHERE rownum <= 3

ORDER by RowNum ASC)

WHERE RECNO = 2

The result is:

ID NAME

Fifth

3. Extract the nth record in a recordset sorted in some way

In the 2 note, when m = N, that is the result of our title. In fact, 2 of the n>m part of the data is basically not used, we are simply to illustrate the convenience of adoption.

As described above, the SQL statement should be:

SELECT Column Name 1 ... Column name N from

     (

SELECT rownum recno, column name 1 ... Column name Nfrom

(SELECT column name 1 ... Column name n from table name order BY column name 1 ... Column name N)

WHERE rownum <= N (number of extracted records)

ORDER by RowNum ASC

     )

WHERE recno = N

Then, the SQL statement for the example in 2 is:

SELECT ID, NAME from

(

SELECT rownum recno, ID, NAME from

(SELECT * from CUSTOMER ORDER by NAME)

WHERE rownum <= 2

ORDER BY RowNum ASC

)

WHERE RECNO = 2

The results are:

ID NAME

Fifth

4. Extract the X record from the beginning of section m of the recordset, sorted in some way

3 is only a case of taking a record, when we need to extract more than one record, at this time the value of N in 2 should be in the range of n >= (M + X-1), when the most economical value is to wait for the good time. Of course the final extraction condition is not RECNO = n, it should be recno BETWEEN m and (M + X-1), so the following SQL statement is:

SELECT Column Name 1 ... Column name N from

    (

SELECT rownum recno, column name 1 ... Column name Nfrom

      (

SELECT Column Name 1 ... Column name n from table name order BY column name 1 ... Column name N)

WHERE rownum <= N (n >= (M + X-1))

ORDER by RowNum ASC

      )

WHERE recno BETWEEN