Parsing Oracle ROWNUM

For rownum, it is the number of the row returned from the query that is sequentially allocated by the Oracle system. The first row is allocated 1, and the second row is 2, 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.
Example:
Example Table: Student (student) table with the following structure:
Id char (6) -- Student ID
Name varchar2 (10) -- name
Create Table student (ID char (6), name varchar2 (100 ));
Insert into sale values ('20140901', 'zhang yi ');
Insert into sale values ('20140901', 'wang 2 ');
Insert into sale values ('20170101', 'Lee 3 ');
Insert into sale values ('20140901', 'zhao si ');
Commit;

(1) rownum for query conditions equal to a certain value
If you want to find the information of the first student in the student table, you can use rownum = 1 as the condition. However, if you want to find the second student information in the student table, you cannot find the data using rownum = 2. Because rownum starts from 1, but the natural numbers above 1 are regarded as false when rownum is equal to or equal to the true value. Therefore, rownum = N (The Natural Number of n> 1) cannot be found ).
SQL> select rownum, ID, name from student where rownum = 1; (it can be used to limit the number of returned records to ensure no error, for example, implicit cursor)
SQL> select rownum, ID, name from student where rownum = 1;
ROWNUM ID NAME
-------------------------------------------------------------------
1 200001 Zhang Yi
SQL> select rownum, id, name from student where rownum = 2;
ROWNUM ID NAME
-------------------------------------------------------------------

(2) rownum for query conditions greater than a certain value
If you want to find the record from the second row, when rownum> 2 is used, the record cannot be found because rownum is a pseudo column always starting from 1, oracle considers that the condition rownum> n (Natural Number of n> 1) is still not true, so records cannot be found.
SQL> select rownum, id, name from student where rownum> 2;
ROWNUM ID NAME
-------------------------------------------------------------------
Then how can we find the record after the second row. You can use the following subquery method to solve the problem. Note that the rownum in the subquery must have an alias; otherwise, the record is not found because rownum is not a column of a table. If the alias cannot be found, you cannot know whether rownum is a subquery column or a primary query column.
SQL> select * from (select rownum no, id, name from student) where no> 2;
NO ID NAME
-------------------------------------------------------------------
3 200003 Li San
4 200004 Zhao Si
SQL> select * from (select rownum, id, name from student) where rownum> 2;
ROWNUM ID NAME
-------------------------------------------------------------------

(3) rownum for query conditions smaller than a certain value
If you want to find the previous record of the third record, use rownum <3 to get two records. Obviously, rownum considers the condition of rownum <n (Natural Number of n> 1) as true, so records can be found.
SQL> select rownum, ID, name from student where rownum <3;
Rownum ID name
-------------------------------------------------------------------
1 200001 Zhang Yi
2 200002 Wang 'er
To sum up the preceding situations, you may need to query rownum data in a certain range. What should you do? We can see that the rownum query condition for a value smaller than a certain value is true, rownum is regarded as false for query conditions greater than a certain value, but it can be converted to true indirectly. Subquery is required. For example, to query the data of rownum between the second row and the third row, including the data of the second row and the third row, we can only write the following statement to first let it return the record rows smaller than or equal to three, then, in the primary query, it is judged that the alias column of the new rownum is greater than or equal to two record rows. However, such operations will affect the speed in the big 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 Li San

(4) rownum and sorting
In Oracle, rownum is the sequence number generated when data is retrieved. Therefore, you must pay attention to the specified rowmun rows of data to be sorted.
SQL> select rownum, id, name from student order by name;
ROWNUM ID NAME
-------------------------------------------------------------------
3 200003 Li San
2 200002 Wang 'er
1 200001 Zhang Yi
4 200004 Zhao Si
It can be seen that rownum is not the serial number generated by the name column. The system assigns the number of the record row according to the sequence in which the record is inserted, and the rowid is also allocated sequentially. To solve this problem, you must use the subquery
SQL> select rownum, id, name from (select * from student order by name );
Rownum ID name
-------------------------------------------------------------------
1 200003 Li San
2 200002 Wang 'er
3 200001 Zhang Yi
4 200004 Zhao Si
In this way, the sequence is sorted by name, and the correct sequence number (from small to large) is marked with rownum)
I have a table with millions of records in my work. To display the table by page on the JSP page, we will consider using rownum. The following is a specific method (each page
20 ):
"Select * From tabname where rownum <20 order by name", but it is found that Oracle cannot be executed according to its own wishes, but it is first casual
Take 20 records, and then order by. After consulting Oracle, it is said that rownum is indeed like this. If you want to use it, you can use subqueries to sort the records first, and then
Rownum, the method is as follows:
"Select * from (select * From tabname order by name) Where rownum <20", but the efficiency is much lower.
After the author's experiment, we only need to add a primary key or index to the order by field to let oracle first sort by this field, and then rownum; the method remains unchanged:
"Select * From tabname where rownum <20 order by name"

Obtains 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 five records:

Select * From tablename where rownum <6; (or rownum <= 5 or rownum! = 6)
If you want to return records 5-9:

Select * From tablename
Where...
And rownum <10
Minus
Select * From tablename
Where...
And rownum <5
Order by name
After the results are selected, the results are sorted by name. (Select first and then sort)

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

Select * From tablename where rownum! = 10; the first nine records are returned.
Cannot be used:>,> =, =, Between... and. Since rownum is a pseudo column that always starts from 1, Oracle considers this condition invalid and cannot find the record.

In addition, this method is faster:

Select * from (
Select rownum r, a from yourtable
Where rownum <= 20
Order by name)
Where r> 10
In this way, the 11-20 records are retrieved! (Select, sort, and then select)

To sort and then select, select Nesting is required: the outer layer of the inner layer is selected.
Rownum is generated with the result set. Once generated, it will not change. At the same time, the generated results are incremental in sequence, and there will never be 2 without 1!
Rownum is a pseudo column generated when the query set is generated. If the where condition contains the rownum condition, then:

1: If the condition is a constant, then:
Only rownum = 1, <= a natural number greater than 1, = A number greater than 1 has no results, and a number greater than one has no results.
That is, when a rownum does not meet the condition, the query ends. this is stop key!

2: when the judgment value is not a constant
If the condition is = var, the condition is met only when var is 1. In this case, the stop key does not exist. full scan is required to determine the data that meets other where conditions.
Select a row before selecting rows with rownum = 2 ......