Oracle's rownum use

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

(1) rownum for query conditions 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 information of the second student in the Student's table, the data was not found using rownum=2 results. Because rownum are all starting from 1, but more than 1 of the natural number in rownum do equal to the judgment is considered false condition, so can not find rownum = N (n>1 natural number).
Sql> Select Rownum,id,name from student where rownum=1; (can be used to limit the number of records returned, guaranteed to be error-prone, such as: implicit cursors)
Sql> Select Rownum,id,name from student where rownum = 2;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------

(2) rownum for query criteria greater 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 from 1. oracle  that the condition of rownum> n (natural number of n>1) is still not established, so the record is not found.

finds records after the second row can be resolved using the following subquery method. Note that the rownum in a subquery must have an alias, or the record will not be detected, because rownum is not a column of a table, and if you cannot alias it, you cannot know whether rownum is a column of a subquery or a column of a primary query.
Sql>select * from (select rownum no ,id,name from student)  where  no>2;
        no id     name
----------  ------ ---------------------------------------------------
          3 200003  lie triple
         4 200004   Zhao Four

(3) rownum for a query condition that is less than a value
RowNum is considered established for rownum<n (natural number of n>1), so a record can be found.
sql> select rownum,id,name from student where rownum <3;
    rownum id     name
---------- ------ -- -------------------------------------------------
        1  200001  Zhang
        2 200002  Wang er

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

(4) RowNum and sorting
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.
Sql> Select RowNum, id,name from student order by name;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
3 200003 Lie triple
2 200002 Wang ER
1 200001 Zhang One
4 200004 Zhao Si
As you can see, rownum is not the ordinal number generated by the Name column. The system is the number of records in the order in which records are inserted, and ROWID is assigned sequentially. In order to solve this problem, we must use sub-query;
Sql> Select RowNum, Id,name from (SELECT * from Student order by name);
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200003 Lie triple
2 200002 Wang ER
3 200001 Zhang One
4 200004 Zhao Si
This will be sorted by name, and the correct sequence number (small to large) is marked with rownum.
The author has a millions record in the work of the table, in the JSP page to the table to be paged display, then consider using rownum to do, the following is the specific method (each page shows 20):
"SELECT * from TabName where rownum<20 order by name" but found that Oracle could not execute its own wishes, but instead took 20 records, then order by, and consulted Oracle, Say rownum really so, want to use, can only use sub-query to achieve first sort, after rownum, method is as follows:
"SELECT * FROM (SELECT * to TabName order by name) where Rownum<20", but this will be much less efficient.
After the author experiment, simply add the primary key or index to the field of order by so that Oracle can sort by the field first, then rownum; The method does not change: "SELECT * from TabName where rownum<20 order by name"

Get the nth-largest 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 to the first 5 records:

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

SELECT * FROM tablename
where ...
and rownum<10
Minus
SELECT * FROM tablename
where ...
and rownum<5
Order BY name
The results are sorted by name when the result is selected. (Select and reorder)

Note: Use only the above symbols (<, <=,! =).

SELECT * FROM tablename where rownum! = 10; Returns the first 9 records.
Not available: >,>=,=,between...and. Because RowNum is a pseudo-column that always starts at 1, Oracle considers this condition to be non-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 reorder and then select)

To sort the re-select, you must use Select nesting: Inner sort outer selection.
RowNum is generated with the result set, once generated, will not change, at the same time, the results are sliding scale in turn, No 1 will never have 2!
RowNum is a pseudo-column produced during the query collection, and if the rownum condition exists in the Where condition:

1: If the criterion is constant, then:
can only rownum = 1, <= is greater than 1 of the natural number, = more than 1 of the number is no result, more than one number is not the result
That is, when a rownum does not meet the condition, then the query ends this is stop key (an unsatisfied, the system will filter out the record, then the next record of the rownum or this, so the following will no longer have to meet the record, this is the Stop key);

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

If the condition is = Var, then only when the Var is 1 satisfies the condition, this time does not have the stop key, must carry on the full scan, to each satisfies the other where condition the data to decide, selects a row to select the rownum=2 line ...

The following excerpt from the China it Laboratory

1. Implementing select TOP N in Oracle

Because Oracle does not support the SELECT top statement, the query for select top n is often implemented in Oracle with the combination of order by and RowNum.

To put it simply, the implementation method is as follows:

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 is an example to illustrate briefly.

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

ID NAME

First

Second

Third

Forth, Geneva

Fifth

Sixth

Seventh

Eighth

Ninth

Ten Tenth

Last

The SQL statement for the first three customers is drawn according to the letter of name as follows:

SELECT * FROM

(SELECT * from CUSTOMER ORDER by NAME)

WHERE ROWNUM <= 3

ORDER by ROWNUM ASC

The output is:

ID NAME

Eighth

Fifth

First

2. Extract the first m (M <= N) record in the top N record

RowNum is a hidden sub-segment of the data number in the record table, so you can extract the recorded rownum when you get the top n record, and then extract the record numbered m from the N record, even if we want to get the result.

From the above analysis it is easy to 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 same data from the table above, the SQL statement that gets the information of the second customer sorted alphabetically by name should read:

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 a certain way

In the 2 description, when m = N, that is the result of our title. In fact, 2 of the practice in the n>m part of the data is basically not used, we just to illustrate the convenience of the use.

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

So, 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 result is:

ID NAME

Fifth

4. Extract the X record from the beginning of the record in the recordset that is sorted in a certain way

3 is only the case of extracting 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 the time to take a good time. Of course the final extraction condition is not RECNO = n, it should be recno between M and (M + X-1), so the resulting 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 M and (M + X-1)

Also take the above data as an example, the 2nd record that extracts the letter of name starts with the SQL statement of 3 records:

SELECT ID, NAME from

(

SELECT ROWNUM RECNO, ID, NAME from

(SELECT * from CUSTOMER ORDER by NAME)

WHERE ROWNUM <= (2 + 3-1)

ORDER by ROWNUM ASC

)

WHERE RECNO between 2 and (2 + 3-1)

The results are as follows:

ID NAME

Fifth

First

Forth, Geneva

Based on this, and then expand the word, as a stored procedure, the beginning of the number of records and the number of records extracted as parameters, it is easy to achieve page extraction data.

Of course, the above are some of the most basic, practical applications are often not so simple, but anyway, no matter how complex applications are always composed of these simple elements, mastering some of the most basic method is always important.

Summary of rownum usage in ORACLE!
For Oracle's rownum problem, a lot of data are said to not support >,>=,=,between...and, only with the above symbols (<, <=,! =), not to say >,& Gt;=,=,between. and will prompt SQL syntax error, but often do not find a record, but also appear to be inexplicable results, in fact, you just understand the meaning of this rownum pseudo-column should not be surprised, the same pseudo-column, rownum and ROWID can be somewhat different, the following examples illustrate

Suppose a table T1 (C1) has 20 records

If you use the Select Rownum,c1 from t1 where RowNum < 10, as long as it is less than the number, the results can easily be found with the general understanding in the conceptual agreement, there should be no doubt.

But if you use select Rownum,c1 from t1 where rownum > 10 (If you write such a query, it should be in your mind to get the next 10 records in your table), you will find that the results you have shown will disappoint you. You may also wonder if someone deleted some records and then looked at the number of records, still 20? So where is the problem?

Understand the meaning of rownum first. Because RowNum is a pseudo-column added to the result set, the result set is followed by a column (emphasis: The result set first). Simply put, the rownum is the serial number that matches the conditional result. It always starts at 1. So your chosen result cannot be no more than 1, and there are other values greater than 1. So you can't expect to get the following result set:

Aaaaaaaa
bbbbbbb
Ccccccc
.................

RowNum >10 no record, because the first one does not meet the removal, the second of the rownum 1, so never meet the conditions of the record. Or you can understand this:

RowNum is a sequence that is the order in which the Oracle database reads data from a data file or buffer. It gets the first record then the rownum value is 1, the second one is 2, and so on. If you use >,>=,=,between...and these conditions, because the rownum of the first record obtained from the buffer or data file is 1, it is deleted, then the bar is removed, but its rownum is still 1, deleted, and so on, without data.

With the concept of rownum that has been built up from different aspects, we can come to know some of the current images using rownum

1. Select rownum,c1 from t1 where rownum! = 10 is the first 9 data returned? Is it the same as the result set returned by the Select Rownum,c1 from tablename where RowNum < 10?
Because after the 9th record is displayed after the query to the result set, the subsequent records are also! = 10, or >=10, so only the previous 9 records are displayed. It can also be understood that rownum for the record of 9 after the rownum for 10, because the condition is!=10, so remove, then record to fill, RowNum is 10, also removed, if down will only show the preceding 9 records

2. Why RowNum >1 when a record is not found, and rownum >0 or rownum >=1 is always displayed so the record
Because RowNum is added after the query to the result set, it always starts at 1

3. Why between 1 and 10 or between 0 and 10 can find results, but with between 2 and 10 without results
Same reason, because rownum always starts from 1.

From the above can be seen, any time want to RowNum = 1 This record abandoned is not right, it is indispensable in the result set, less rownum=1 like castles in the castle can not exist, so your rownum conditions to include 1

But if you want to use RowNum > 10, then use nested statements, put Mr. RowNum, and then query him.
SELECT *
From (Selet rownum as rn,t1.* from a where ...)
where RN >10

Paging through the result set in general code is the way to do it.

In addition: both ROWID and rownum are called pseudo-columns, but they exist in a different way, ROWID can be said to be physically present, representing the unique location ID recorded in the tablespace, unique in the DB. As long as the record has not been moved, ROWID is unchanged. rowID is like a normal column in a table relative to a table, so there will be no rownum that happens if ROWID is the condition.
Also note: RowNum cannot be prefixed with the name of any base table.

Oracle's rownum use