Principles and usage of Oracle rownum and oraclerownum

Principles and usage of Oracle rownum and oraclerownum
For each row returned by the query, a number is returned using the rownum pseudo column, indicating the order in which oracle selects rows from the table or adds rows.

The first row rownum is 1, the second row is 2, and so on.

You can use rownum to limit the number of rows returned by the query, as shown in the following example:

select * from test where rownum < 10;

If an order by clause and rownum are in the same query, the rows will be re-ordered by the order by clause. See the following example.

select rownum, name, address, birthday  from test where rownum < 5 order by birthday;

We found that rownum is not sequential. The system inserts the number for the record. To solve this problem, we need to use subqueries.

select rownum, name, address, birthday  from (select name, address, birthday from test order by birthday) where rownum < 5

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

Previously we used rownum <10 to query 9 data records. This time we used rownum> 1 to query the data.

Select * from test where rownum> 1;

No data is returned for this query, so we cannot use rownum = n or rownum> n (Natural Number of n> 1) for the query.

The reason is:

The first row of reading is allocated as 1, and rownum> 1 makes the condition false. Then, the second row is read and is now allocated as 1.

Rownum causes the condition to remain false. All rows subsequently fail to meet the condition, so no row is returned.

If rownum must be greater than a positive integer for query, subquery is required.

select num, name, address, birthday  from (select rownum as num, name, address, birthday from test) where num > 5;

You can also use between... and...

select num, name, address, birthday  from (select rownum as num, name, address, birthday from test) where num between 5 and 10

SQL test script:

create table test(    name varchar2(100),    address varchar2(100),    birthday date);insert into test values('name1','address1',to_date('1990-04-10','yyyy-MM-dd'));insert into test values('name2','address2',to_date('1989-06-11','yyyy-MM-dd'));insert into test values('name3','address3',to_date('1991-08-23','yyyy-MM-dd'));insert into test values('name4','address4',to_date('1998-12-04','yyyy-MM-dd'));insert into test values('name5','address5',to_date('1985-07-16','yyyy-MM-dd'));insert into test values('name6','address6',to_date('1988-04-08','yyyy-MM-dd'));insert into test values('name7','address7',to_date('1992-08-26','yyyy-MM-dd'));insert into test values('name8','address8',to_date('1995-11-10','yyyy-MM-dd'));insert into test values('name9','address9',to_date('1988-03-28','yyyy-MM-dd'));insert into test values('name10','address10',to_date('1982-05-02','yyyy-MM-dd'));insert into test values('name11','address11',to_date('1991-10-19','yyyy-MM-dd'));

Reference File

Https://docs.oracle.com/cd/E18283_01/server.112/e17118/pseudocolumns009.htm

Author: itmyhome