• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1574
  • Last Modified:


How to execute SQL command with (ROWNUM > i and ROWNUM <j) expression?

Thanks and best rgds,
Vinh, DangHa
1 Solution
Hey vinhdh,

It is simple.
Just include an expression of rownum in you WHERE clause.
"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.
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.
vinhdhAuthor Commented:
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.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


 Try the foll:

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

There's a trick to do:

select * from (select rownum my_rownum, a.* from tab a where rownum < 10) where my_rownum > 5;
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.
vinhdhAuthor Commented:
Thank you very much.
u could try thee following

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now