Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ROWNUM?

Posted on 1999-07-17
8
Medium Priority
?
1,561 Views
Last Modified: 2008-03-17
Hi,
How to execute SQL command with (ROWNUM > i and ROWNUM <j) expression?

Thanks and best rgds,
Vinh, DangHa
0
Comment
Question by:vinhdh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 3

Accepted Solution

by:
doronla earned 100 total points
ID: 1087547
Hey vinhdh,

It is simple.
Just include an expression of rownum in you WHERE clause.
examp:
"SELECT * FROM emp WHERE rownum < 4;"

This query will bring back not more than 3 rows.
You can execute it from SQL*PLUS, or from wherever you want.

Note that rownum is working BEFORE the ORDER BY clause (if there is any). This mean that if you want to get the 5 employees that have highest salaries, a query like that:
"SELECT * FROM emp WHERE rownum <= 5 ORDER BY salary;"
will bring some 5 employees, sorted by thier salary.

Good Luck.
0
 
LVL 6

Expert Comment

by:mshaikh
ID: 1087548
The answer to your question is:

There is NO WAY to execute SQL command with (ROWNUM > i and ROWNUM <j) expression

You can ONLY do (ROWNUM <j) in an SQL statement.

Howler, if you have any more questions regarding this.
0
 

Author Comment

by:vinhdh
ID: 1087549
Thanks for your help,

I execute query "SELECT * FROM myTable", it return about 500 records. I don't want to process all this records in this time. So I'd like to get about 100 records for each time.

If I use 'ROWNUM<=100', I don't know to get next 100 records for next time.

Please help me.
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 1

Expert Comment

by:bkesarla
ID: 1087550
Hi,

 Try the foll:

select * from table_name where
rowid >=
  (select max(rowid) from fnd_application
  where
 rownum<=<lower value of the range>)
and rowid <=
  (select max(rowid) from table_name
  where
 rownum<=<Higher value of the Range>)


0
 
LVL 2

Expert Comment

by:Hauke
ID: 1087551
There's a trick to do:

select * from (select rownum my_rownum, a.* from tab a where rownum < 10) where my_rownum > 5;
0
 
LVL 6

Expert Comment

by:mshaikh
ID: 1087552
If you are not going to need ORDER BY clause, that is if the order of the retrieved rows is unimportant then you can try:

SELECT column1, column2,....
FROM (SELECT ROWNUM_A, column1, column2 ...
        FROM myTable A) B
WHERE B.ROWNUM_A >= your_lower_limit
  AND B.ROWNUM_A < your_upper_limit

Now if you wanted the rows to be returned in any particular order, this won't work. If you need a particular order in the returned rows, post you columns you need ordered and I will help you with the query for that.
0
 

Author Comment

by:vinhdh
ID: 1087553
Thank you very much.
0
 

Expert Comment

by:savvyg
ID: 1087554
u could try thee following

select * from tab a  where
higher_no <
(select count(*) from tab b where
b.rowid<a.rowid)
and lower_no >=
(select count(*) from tab c
where c.rowid>=b.rowid);
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question