Oracle rownum principles and use

for each row returned by the query, use the rownum pseudo-column to return a number indicating the order in which Oracle selects rows from a table or joins rows.

The first line of the selection is rownum 1, second behavior 2, and so on.

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

SELECT * FROM Test where rownum < 10;

if an ORDER BY clause and rownum are in the same query, then the rows are reordered 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 is to follow the record when inserting the number of records, to solve this problem requires the use of sub-query

Select RowNum, name, address, birthday from  (select name, address, birthday from Test order by birthday) where rownum < 5

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

We used rownum<10 to find out 9 data, this time we use rownum>1 to query the data

SELECT * FROM Test where rownum > 1;

found that the query returned no data, so we cannot use rownum = N or rownum > N (Natural number of n>1) to query

The reasons are:

The first line read is assigned as 1,rownum>1 so that the condition is false, and then the second line is now the first row, and also assigned to 1

RowNum makes the condition still false, and all rows subsequently fail to satisfy the condition, so no rows are returned.

If you want to rownum more than a positive integer to query, you also need a subquery to implement

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, address varchar2 (+), birthday date); INSERT into test values (' name1 ', ' add Ress1 ', 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 ') and insert into test values (' Name11 ', ' Address11 ', to_date (' 1991-10-19 ', ' yyyy-mm-dd '));

Reference Files

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

Itmyhome

Oracle rownum principles and use