How can I use rownum to limit the number of rows returned by a query?

How can I use rownum to limit the number of rows returned by a query?

Software environment:

1. Windows NT4.0 + Oracle 8.0.4

2. Oracle installation path: C: \ orant

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 records 5-9, sorted by month
SQL> select * from (select rownum row_id, month, distinct
2 from (select month, distinct from sale group by month, distinct)
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

ORACLE: rownum does not order

Oracle provides the rownum feature. For each record returned by a query, rownum returns a number to indicate the order. This list is not sorted.

SQL> select username, rownum from dba_users order by username;

Username rownum

--------------------------------

Aurora $ orb $ unauthenticated 6

Ctxsys 10

Dbsnmp 4

MDSYS 9

Ops $ Oracle 5

Ordplugins 8

Ordsys 7

Outln 3

Sys 1

System 2

Testuser 11

11 rows selected.
using order by does not solve the problem, because rownum has been applied before sorting records. the following is an example of returning the first three records of a basic table.

Select username, rownum from dba_users

Where rownum <4 order by username;

Username rownum

--------------------------------

Outln 3

Sys 1

System 2

------------

Tim Quinlan