Turn: The rownum in Oracle cannot be greater than>

Title: rownum cannot be greater than> in Oracle

Switch from the network and make slight changes:

1. Description of rownum

For the rownum problem of Oracle, many documents say that ">,> =, =, between..." in SQL statements are not supported... and "operator. Only the following operators can be used: <, <= ,! = ",

Not to say ">,> =, =, .. "and" will prompt SQL syntax errors, but it is often unable to find a record, and it may appear inexplicable results.

In fact, we should not be surprised to understand the meaning of this rownum pseudo column.

Both rowid and rownum are called pseudo columns, but they exist in different ways:

Rowid is physical, indicating the unique location ID of the record in the tablespace, which is unique in the DB. As long as the record has not been moved, the rowid remains unchanged.

Compared with a table, rowid is similar to a common column in a table. Therefore, rowid is not used as a condition to show the inexplicable results of rownum.

In addition, you must note that rownum cannot use any base table name as the prefix.

For the following SQL statements

SQL> select rownum, ID, age, name from loaddata where rownum> 2;

Rownum ID age name
----------------------

Rownum> 2, no records found.

Because rownum always starts from 1. If the first row does not meet the requirements, the rownum of the second row becomes 1. So there is no matching record.

It can be understood that rownum is a sequence in which Oracle databases read data from data files or buffers.

If it obtains the first record, the rownum value is 1 and the second record is 2. And so on.

When ">,> =, =,... and ", The rownum of the first record obtained from the buffer or data file is 1. If it does not meet the SQL statement conditions, it will be deleted and then removed.

The rownum of the next row will be 1, deleted, and so on, and no data will be available.

Ii. Analysis of Several Phenomena in rownum usage

With the concept of rownum established from different aspects above, we will understand several phenomena of using rownum:

(1) Select rownum, ID, age, name from loaddata where rownum! = 10 why is the first nine data records returned?
Why is it the same as the result set returned by select rownum, ID, age, name from loaddata where rownum <10?

Because after the result set is queried and 9th records are displayed, all subsequent records are displayed! = 10 or> = 10, so only the first nine records are displayed.

It can also be understood that after rownum is 9, The rownum of the retrieved record is 10, because the condition is! = 10, So delete it. Then, take the next row, and delete the rownum value 10. And so on.

Therefore, only the first nine records are displayed.

(2) When rownum> 1 is not found, rownum> 0 or rownum> = 1 shows all records.

This is because rownum is added after the queried result set, which always starts from 1.

(3) Why between 1 and 10 or between 0 and 10 can be found, but between 2 and 10 cannot be obtained.

The same reason: rownum always starts from 1.

From the above, it is wrong to discard the rownum = 1 record at any time. It is indispensable in the result set.

If rownum = 1 is missing, it is like a castle in the air. Therefore, the rownum condition must include 1.

Iii. Examples of rownum usage:

-----------
-- SQL table creation script

Create Table loaddata
(
Id varchar2 (50 ),
Age varchar2 (50 ),
Name varchar2 (50)
);
-----------

(1) rownum query conditions for "equal to a value"

If you want to find the information of the first record in the loaddata table, you can use rownum = 1 as the condition.

However, if you want to find the information of the second record in the loaddata table, you cannot find the data by using rownum = 2.

Because rownum starts from "1.

The natural number above "1" is considered to be false when rownum is equal to or equal to the value, so rownum = N (The Natural Number of n> 1) cannot be found ).

Select rownum, ID, age, name
From loaddata
Where rownum = 1; -- it can be used to limit the number of returned records to avoid errors, for example, implicit cursor.

SQL> select rownum, ID, age, name from loaddata where rownum = 1;

Rownum ID age name
----------------------
1 200001 22 aaa

SQL> select rownum, ID, age, name from loaddata where rownum = 2;

Rownum ID age name
----------------------

Note: SQL> select rownum, ID, age, name from loaddata where rownum! = 3; -- the first two records are returned.

Rownum ID age name
----------------------
1 200001 22 aaa
2 200002 22 bbb

(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, no record can be found.

The reason is that rownum is a pseudo column that always starts from 1. Oracle considers that rownum> N (Natural Number of n> 1) is still not true, so no records can be found.

SQL> select rownum, ID, age, name from loaddata where rownum> 2;

Rownum ID age name
----------------------

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, no records will be found because rownum is not a column in a table.

If you cannot afford the alias, you cannot know whether rownum is a subquery column or a primary query column.

SQL> select rownum, ID, age, name from (select rownum No, ID, age, name from loaddata) where no> 2;

Rownum ID age name
----------------------
3 200003 22 ccc
4 200004 22 ddd
5 200005 22 eee
6 200006 22 aaa

SQL> select * from (select rownum, ID, age, name from loaddata) Where rownum> 2;

Rownum ID age 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, age, name from loaddata where rownum <3;

Rownum ID age name
----------------------
1 200001 22 aaa
2 200002 22 bbb

To sum up the following situations:

It may be sometimes necessary to query the data of rownum in a certain range. From the above, we can see that the query condition for rownum smaller than a certain value is true.

Rownum directly considers the query condition greater than a certain value as false, but it can be converted to true indirectly. Then, subquery must be used.

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, you can only write the following statement to first return the record rows smaller than or equal to three,

Then, in the primary query, it is determined that the "alias column" of the new rownum is greater than or equal to two record rows. However, such operations will affect the retrieval speed in the big data set.

SQL> select * from (select rownum No, ID, age, name from loaddata where rownum <= 3) where no> = 2;

Rownum ID age name
----------------------
2 200002 22 bbb
3 200003 22 ccc

You can also use this method:

SQL> select rownum, ID, age, name from loaddata where rownum <4
Minus
Select rownum, ID, age, name from loaddata where rownum <2

Rownum ID age name
----------------------
2 200002 22 bbb
3 200003 22 ccc

 

(4) rownum and sorting

Rownum in Oracle is the sequence number generated when data is retrieved. Therefore, if you want to retrieve the specified rowmun row of data in sorted data, pay attention to it.

Prerequisites: five records have been inserted in the loaddata table, and the last record ID is 200005. Then, insert into loaddata values ('20170101', '22', 'aaa ');

SQL> select rownum, ID, age, name from loaddata;

Rownum ID age name
----------------------
1 200001 22 aaa
2 200002 22 bbb
3 200003 22 ccc
4 200004 22 ddd
5 200005 22 eee
6 200006 22 aaa

SQL> select rownum, ID, age, name from loaddata order by name;

Rownum ID age name
----------------------
1 200001 22 aaa
6 200006 22 aaa
2 200002 22 bbb
3 200003 22 ccc
4 200004 22 ddd
5 200005 22 eee

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, age, name from (select * From loaddata order by name );

Rownum ID age name
----------------------
1 200001 22 aaa
2 200006 22 aaa
3 200002 22 bbb
4 200003 22 ccc
5 200004 22 ddd
6 200005 22 eee

In this way, the sequence is sorted by name, and the correct sequence number (from small to large) is marked with rownum ).

When there is a large amount of data, we recommend that you add a primary key or index to the order by field to improve the efficiency.

Similarly, an intermediate record set is returned:

SQL> select * from (select rownum Ro, ID, age, name from loaddata where rownum <5 order by name) where Ro> 2; (select, sort, and then select)

Rownum ID age name
----------------------
3 200002 22 bbb
4 200003 22 ccc

In general, sorting is required before returning the intermediate record set:

SQL> select * from (select T. *, rownum Ro from (select ID, age, name from loaddata order by name) t where rownum <5) where Ro> = 2; (sort and select again)

Rownum ID age name
----------------------
3 200002 22 bbb
4 200003 22 ccc

Note that the SQL statement syntax at this time uses multiple (layer-3) nesting. Note: rownum uses the "column alias ".
 
In fact, this statement is also a classic SQL statement page in the Oracle dataset.Algorithm: First sort, then select rownum <the maximum value of a page, and then select rownum> the minimum value of a page.

 

4. One instance:

Requirement: if you do not know the data rules and quantity in the database, print all the student data to the terminal.

Solution: rownum is a pseudo column and does not exist in the table. The database first executes the from book traversal to the book table.

If there is no where filter condition, a result set is first generated, and then the conditions after the SELECT statement are viewed. The appropriate fields are selected to form the final result set.

If the where condition exists, the items that do not meet the condition will be deleted from the first result set, and the subsequent data will be added for judgment.

Therefore, if rownum = 2 or rownum> 10 is directly written, no data can be found.

You can use a subquery to solve this problem: for select rownum, ID from book where rownum = 2; no data can be found.

declare
v_number binary_integer;
v_student student % rowtype;
begin
select count (*) into v_number from student;
for I in 1 .. v_number loop
select ID, name, age into v_student from (select rownum RN, ID, name, age from student) Where Rn = I;
dbms_output.put_line ('Id: '| v_student.id | 'name:' | v_student.name);
end loop;
end;

Rownum is a pseudo column added to the result set, that is, a column added after the result set is found (emphasis: a result set must be created first ).

In short, rownum is the serial number that matches the condition result.

It always starts from 1. Therefore, the selected result cannot be less than 1, but there are other values greater than 1.

[-End-]

Jrq 2011/08/03