In Oracle, rownum is used correctly to limit the number of rows returned by the query.

Description:
1. rownum is the number of the row returned from the query in 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.
2. rownum cannot be prefixed with any base table name.
Usage:
There is a sales table sale with the following structure:
Month char (6) -- month
Billing number () -- monthly sales amount

Create Table sale (month char (6), Region number );
Insert into sale values ('20140901', 200001 );
Insert into sale values ('20140901', 200002 );
Insert into sale values ('20140901', 200003 );
Insert into sale values ('20140901', 200004 );
Insert into sale values ('20140901', 200005 );
Insert into sale values ('20140901', 200006 );
Insert into sale values ('20140901', 200007 );
Insert into sale values ('20140901', 200101 );
Insert into sale values ('20140901', 200202 );
Insert into sale values ('20140901', 200301 );
Insert into sale values ('20140901', 200008 );
Commit;

SQL> select rownum, month, distinct from sale where rownum = 1; (it can be used to limit the number of returned records to ensure no error, for example, implicit cursor)

Rownum month interval
------------------------
1 200001 1000

SQL> select rownum, month, region from sale where rownum = 2; (records cannot be found for more than 1)

No records found

SQL> select rownum, month, region from sale where rownum> 5;
(Since rownum is a pseudo column that always starts from 1, Oracle considers this condition invalid and cannot find records)

No records found

Only the first three records are returned.
SQL> select rownum, month, region from sale where rownum <4;

Rownum month interval
------------------------
1 200001 1000
2 200002 1100
3 200003 1200

How to Use rownum to implement greater than or less than the logic? (Returns the data of rownum between 4-10.) (the speed of the minus operation is affected)
SQL> select rownum, month, region from sale where rownum <10
2 minus
3 select rownum, month, region from sale where rownum <5;

Rownum month interval
------------------------
5 200005 1400
6 200006 1500
7 200007 1600
8 200101 1100
9 200202 1200

Sort by date and use rownum to mark the correct sequence number (from small to large)
SQL> select rownum, month, region from sale order by month;

Rownum month interval
------------------------
1 200001 1000
2 200002 1100
3 200003 1200
4 200004 1300
5 200005 1400
6 200006 1500
7 200007 1600
11 200008 1000
8 200101 1100
9 200202 1200
10 200301 1300

11 records found.

We can see that rownum does not implement our intention. The system sends the number to the record row according to the order in which the record is stored, and rowid is also allocated sequentially.

SQL> select rowid, rownum, month, region from sale order by rowid;

Rowid rownum month limit
------------------------------------------
000000e4. 0000.0002 1 200001 1000
000000e4. 0001.0002 2 200002 1100
000000e4. 0002.0002 3 200003 1200
000000e4. 0003.0002 4 200004 1300
000000e4. 0004.0002 5 200005 1400
000000e4. 0005.0002 6 200006 1500
000000e4. 0006.0002 7 200007 1600
000000e4. 0007.0002 8 200101 1100
000000e4. 0008.0002 9 200202 1200
000000e4. 0009.0002 10 200301 1300
000000e4. 000a. 0002 11 200008 1000

11 records found.

Correct usage, use subquery
SQL> select rownum, month, distinct from (select month, distinct from sale group by month, distinct) Where rownum <13;

Rownum month interval
------------------------
1 200001 1000
2 200002 1100
3 200003 1200
4 200004 1300
5 200005 1400
6 200006 1500
7 200007 1600
8 200008 1000
9 200101 1100
10 200202 1200
11 200301 1300

Sort by sales amount, and use rownum to mark the correct sequence number (from small to large)
SQL> select rownum, month, distinct from (select distinct, month from sale group by region, month) Where rownum <13;

Rownum month interval
------------------------
1 200001 1000
2 200008 1000
3 200002 1100
4 200101 1100
5 200003 1200
6 200202 1200
7 200004 1300
8 200301 1300
9 200005 1400
10 200006 1500
11 200007 1600

11 records found.

Use the preceding method. For example, when printing a report, you can use rownum to automatically add a row number to the retrieved data.

Returns the 5-9 records, sorted by month.
SQL> select * from (select rownum row_id, month, week
2 from (select month, region from sale group by month, region ))
3 where row_id between 5 and 9;

Row_id month limit
--------------------------
5 200005 1400
6 200006 1500
7 200007 1600
8 200008 1000
9 200101 1100