How to cut SELECT in ORACLE into pages???

fux23

In MySQL we may use
"SELECT * FROM USERS LIMIT a,b"

where a and b are some INTEGER NUMBERS....
and as a result we may easily make page navigation...

which may look like this:

<<PREVIOUS 5 6 7 8 [9] 10 11 12 13 NEXT>>

HOW TO DO IT IN ORACLE.... we do not have LIMIT function.....

trexx

if i remember right, you have to use something like "SELECT * FROM table WHERE ROWNUM > 0 AND ROWNUM < 100" on oracle databases... ROWNUM does about the same as LIMIT on mysql.

sdjensen

A solution could be:

select from (
select ,rownum row_num from (
select <fields> from <table> where <where> order by <sort>
)
) where row_num between $start_range and $end_range

with this solution the selection will be the same even if you are sorting the slection.

fux23

It does not work.......
I have tried the first example...

SELECT * FROM table WHERE ROWNUM > 0 AND ROWNUM < 100

It is ok when we start at 0 ... like above but when it looks for example like this:

SELECT * FROM table WHERE ROWNUM > 100 AND ROWNUM < 200

It does not return ANYTHING.... and I know that there are a lot of records in that database..... because

SELECT * FROM table

returns me all records...

=================================
As for the second post.....

select from (
select <b>,</b>rownum row_num from (
select name from workers where id > 0 order by id
)
) where row_num between $min and $max

I have such error:
<b>ORA-00923: FROM keyword not found where expected</b>

when I use:

select * from (
select rownum row_num from (
select name from workers where id > 0 order by id
)
) where row_num between $min and $max

I GOT THE RECORDS ... but they are only the ROWNUMBERS... so they are useless.....

Still trying to solve this problem................
Anyone have an Idea???
I'm using ORACLE 8.1.7

sdjensen

my mistake.....try:

$field = "user,password,lastlogin";

select $field from (
select $field,rownum row_num from (
select $field from <table> where <where> order by <sort>
)
) where row_num between $start_range and $end_range

fux23

USE IT LIKE THIS:

<b>select from (
select p.,rownum row_num from (
select <FIELD> from <TABLE> where <WHERE> order by <ORDER>
) p
) where row_num between $min and $max</b>

🙂

thanks sdjensen and trexx for Your help