Go: rownum in Oracle cannot use a problem greater than >

I. Description of the RowNum

With regard to Oracle's rownum problem, many of the data say that the ">, >=, =, between...and" Operators in SQL statements are not supported, and can only be used with the following operation symbols "<, <=,! =",

Not to say ">, >=, =, between." And "will prompt the SQL syntax error, but often does not find a record, but also appears to be the result of inexplicable."

In fact, as long as the understanding of the meaning of this rownum pseudo-column should not be surprised.

rowID and rownum are called pseudo-columns, but they exist in a different way:

rowID is physically present, representing the unique location ID recorded in the tablespace, which is unique in the db. As long as the record has not been moved, ROWID is unchanged.

rowID is a general column in the table relative to the table, so there will be no rownum of those inexplicable results with ROWID as the condition.

Also note: RowNum cannot be prefixed with the name of any base table.

For the following SQL statement

Sql>select rownum,id,age,name from LoadData where rownum > 2;

ROWNUM ID Age NAME
------- ------ --- ------

Rownum>2, no records were queried.

Because rownum always starting from 1, the first one does not meet the removal, the second rownum 1. And so on, so there is never a record that satisfies the condition.

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, the rownum value is 1, and the second one is 2. In turn.

When using the ">, >=, =, between...and" conditions, the first record obtained from the buffer or data file has a rownum of 1, does not conform to the conditions of the SQL statement, is deleted, and then the bar is removed.

The rownum of the next bar will also be 1, deleted, and so on, without data.

Ii. analysis of several phenomena in the use of rownum

With the above concept of rownum from different aspects, we know the following phenomena of using rownum:

(1) Select Rownum,id,age,name from LoadData where rownum! = 10 is the first 9 data returned?
Why is it the same as the result set returned by the Select Rownum,id,age,name from LoadData where RowNum < 10?

Because after the 9th record is displayed after the query to the result set, the next record is! = 10 or >=10, so only the previous 9 records are displayed.

It can also be understood that rownum is 9, take the record of RowNum 10, because the condition is!=10, so delete. Then take down one, its rownum again 10, also deleted. And so on

So only the previous 9 records are displayed.

(2) What RowNum >1 when a record is not found, and rownum >0 or rownum >=1 always show All records.

This is because rownum is in the query to the result set, plus go, it always starts from 1.

(3) Why between 1 and 10 or between 0 and 10 can find results, and between 2 and 10 without results.

The same reason: Because RowNum always starts from 1.

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

Less rownum=1 like castles in the castle can not exist. Therefore, the rownum condition should be included in the 1.

Some examples of rownum practical use:

-----------
--sql Building a table script

CREATE TABLE LoadData
(
ID VARCHAR2 (50),
Age VARCHAR2 (50),
NAME VARCHAR2 (50)
);
-----------

(1) rownum for "equals to a value" query criteria

If you want to find the information for the first record in the LoadData table, you can use Rownum=1 as a condition.

But to find the information of the second record in the LoadData table, using rownum=2, the data cannot be found.

Because rownum starts with "1".

"1" above the natural number, in rownum do equal to the judgment is considered false condition, so can not find rownum = N (natural number of n>1).

Select Rownum,id,age,name
From LoadData
where rownum = 1; --can be used to limit the number of records returned to the place, to ensure that there is no error, such as: implicit cursor.

Sql>select rownum,id,age,name from loaddata where rownum = 1;

ROWNUM ID Age NAME
------- ------ --- ------
1 200001 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 2 records are returned.

ROWNUM ID Age NAME
------- ------ --- ------
1 200001 AAA
2 200002 BBB

(2) RowNum for query conditions that are greater than a value

If you want to find records from the second row of records, when the use of rownum>2 is not found in the record.

The reason is that because rownum is a pseudo-column that always starts at 1, Oracle thinks rownum> n (the natural number of n>1) is still not valid, so the record is not found.

Sql>select rownum,id,age,name from LoadData where rownum > 2;

ROWNUM ID Age NAME
------- ------ --- ------

So how do you find the second line of records?

You can use the following subquery method to resolve.

Note that rownum in a subquery must have an alias, or the record will still not be found because rownum is not a column of a table.

If you do not have an alias, you cannot know whether rownum is a column of a subquery or a column of a main query.

Sql>select rownum,id,age,name from (select RowNum No, id,age,name from LoadData) where no > 2;

ROWNUM ID Age NAME
------- ------ --- ------
3 200003 CCC
4 200004 DDD
5 200005 EEE
6 200006 AAA

Sql>select * FROM (select Rownum,id,age,name from LoadData) where rownum > 2;

ROWNUM ID Age NAME
------- ------ --- ------

(3) RowNum for query conditions that are less than a certain value

If you want to find a third record of previous records, you can get two records when using Rownum<3.

Obviously rownum for rownum<n (the natural number of n>1) is considered to be set up, so records can be found.

Sql> Select Rownum,id,age,name from LoadData where rownum < 3;

ROWNUM ID Age NAME
------- ------ --- ------
1 200001 AAA
2 200002 BBB

In several cases:

May sometimes need to query rownum in a certain interval of data, from the above can be seen rownum to a value less than a query condition is considered true.

RowNum a query condition that is greater than a value is directly considered false, but can indirectly be converted to true, then a subquery must be used.

For example, to query the data between the second and third rows of rownum, including the second row and the third row of data, write only the following statement, first to return a record line less than or equal to three.

Then, in the main query, determine the new RowNum's "alias" is greater than or equal to two of the record line. However, such operations can affect the retrieval speed in a large data set.

Sql>select * FROM (select RowNum no,id,age,name from LoadData where rownum <= 3) where no >= 2; --Must be in the small outside big

ROWNUM ID Age NAME
------- ------ --- ------
2 200002 BBB
3 200003 CCC

It can also be implemented in this way:

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 BBB
3 200003 CCC

(4) RowNum and sorting

The rownum in Oracle is the sequence number that is generated when the data is taken. Therefore, it is important to note that when you request that the specified Rowmun row data be fetched in the sorted data.

Prerequisites: The LoadData table has been insert 5 records, the last record ID is 200005, followed by insert into loaddata values (' 200006 ', ' n ', ' AAA ');

Sql>select rownum,id,age,name from LoadData;

ROWNUM ID Age NAME
------- ------ --- ------
1 200001 AAA
2 200002 BBB
3 200003 CCC
4 200004 DDD
5 200005 EEE
6 200006 AAA

Sql>select rownum, id,age,name from LoadData order by name;

ROWNUM ID Age NAME
------- ------ --- ------
1 200001 AAA
6 200006 AAA
2 200002 BBB
3 200003 CCC
4 200004 DDD
5 200005 EEE

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.

To solve this problem, you must use a subquery

Sql>select rownum, Id,age,name from (SELECT * from LoadData order by name);

ROWNUM ID Age NAME
------- ------ --- ------
1 200001 AAA
2 200006 AAA
3 200002 BBB
4 200003 CCC
5 200004 DDD
6 200005 EEE

This will be sorted by name, and the correct sequence number (small to large) is marked with rownum.

For large data volumes, it is recommended that you increase the efficiency by adding a primary key or index to the field on the order by.

Again, return the middle Recordset:

Sql>select * FROM (select RowNum ro,id,age,name from LoadData where RowNum < 5 order by name) where RO > 2; (Select reorder and then select)

ROWNUM ID Age NAME
------- ------ --- ------
3 200002 BBB
4 200003 CCC

In general business requirements, it is necessary to sort the first and then return to the intermediate Recordset:

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 then select again)

ROWNUM ID Age NAME
------- ------ --- ------
3 200002 BBB
4 200003 CCC

Note The SQL statement at this point, using multiple (three-tier) nesting. Also note: RowNum uses "column aliases".

In fact, the statement is also a classic SQL statement paging algorithm for Oracle Datasets: Sort First, then select RowNum < maximum of a page, and then select RowNum > Minimum value for a page.

四、一个 instances:

Requirements: Assume that you do not know the data rules and quantities in the database, to print all the student data to the terminal.

Solution: RowNum is a pseudo-column, not in the table, the database first executes from the Book Traversal Book table.

If there is no where condition filtering, then a result set is first made, and then the condition after the select is selected, and the appropriate field is chosen to form the final result set.

If there is a where condition, the non-qualifying will be removed from the first result set, and the subsequent data is added to the judgment.

So if you write rownum=2 directly, or rownum>10 such a statement can not find the data.

A subquery can be used 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 that adds to the result set, which is the result set 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. Therefore, the chosen result cannot be no more than 1, but there are other values greater than 1.

Go: rownum in Oracle cannot use a problem greater than >