Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Select a range of rows to be displayed using ROWNUM

wyfwongMar 7 2006 — edited Mar 8 2006
I am trying to select a range of records to be displayed using Rownum
It works using MINUS
SQL> select rownum,department_id,department_name from departments where rownum <= 20
minus
select rownum,department_id,department_name from departments where rownum < 11;

but does not work if a range is specified
select rownum,department_id,department_name from departments where rownum >= 11 and rownum <= 20;
What has gone wrong?
-----------------------------------------------------------------------------------------------------------------------
Details of what I have tried are as follows:
Connect to the sample schema HR
SQL> connect hr/hr
SQL> desc departments
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)

List all records in Departments
SQL> select rownum,department_id,department_name from departments;
ROWNUM DEPARTMENT_ID DEPARTMENT_NAME
---------- ------------- ------------------------------
1 10 Administration
2 20 Marketing
3 30 Purchasing
4 40 Human Resources
etc......
26 260 Recruiting
27 270 Payroll
27 rows selected.

List the first 10 records in DEPARTMENTS
SQL> select rownum,department_id,department_name from departments where rownum <= 10;
ROWNUM DEPARTMENT_ID DEPARTMENT_NAME
---------- ------------- ------------------------------
1 10 Administration
2 20 Marketing
etc.....
10 100 Finance

List row number from 11 to 20, but cannot no rows selected. Why?

SQL> select rownum,department_id,department_name from departments where rownum >= 11 and rownum <= 20;
no rows selected

Use of MINUS can retrieve row number from 11 to 20
SQL> select rownum,department_id,department_name from departments where rownum <= 20
minus
select rownum,department_id,department_name from departments where rownum < 11;

ROWNUM DEPARTMENT_ID DEPARTMENT_NAME
---------- ------------- ------------------------------
11 110 Accounting
12 120 Treasury
13 130 Corporate Tax
14 140 Control And Credit
15 150 Shareholder Services
16 160 Benefits
17 170 Manufacturing
18 180 Construction
19 190 Contracting
20 200 Operations
10 rows selected.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 5 2006
Added on Mar 7 2006
5 comments
24,330 views