Oracle problem

pots

I've recently been handed a project that uses an Oracle database.

I've got a query that returns over a 1000 results, I want to display the results 20 at a time. I've got the query printing out the twenty results fine. Is it then possible for me to tell oracle to ignore the last 20 and then print the twenty after that. I've tried the rownums between method and that's not really what I'm looking for as I need the query to run fully for my next/prev script.

So basically I want to be able to print 1-20 then on the next page ignore 1-20 and print 21-40 and so on.

Sorry if it's a dumb question but I'm reasonably new to PHP and I've never used Oracle before. Any help would be gratefully appreciated.😕

nnichols

I might be wrong (yes, quite possible) but from what I remember on Oracle you can use the following -

select
from ( select from table order by something )
where rownum <= 10

This would allow you to build your page navigation using the same methods as MySQL limit.

Hope this helps (hope I'm correct)

pots

Thanks for your reply but I've tried where rownum > x and rownum < y and it only works if x=0. If I want it to return rows 21 to 40 say, it doesn't return any results. The same thing happens when I say where rownum between x and y.

Any other thoughts?

nnichols

If you sort the results I now it can cause problems because the row nums are assigned before the sort. Try -

SELECT * FROM (your SELECT statement)
WHERE rownum BETWEEN 21 AND 40

I haven't used Oracle for ages but I think that's how I used to do it.

Give it a try 😉

nnichols

Just found this in an article -

Retrieving data based on rownum ranges

If you try the following SELECT you will find it doesn't work

SELECT col1 from table1
WHERE rownum between 8 AND 15

No rows returned

However if you use an in-line view as in

SELECT rn, col1
FROM
(select rownum rn ,col1 from table1)
WHERE rn between 8 and 15

you'll find it works as expected

nnichols

Quite a good explanation here - http://dbforums.com/showthread.php?threadid=586215

Hope you get it sorted

pots

Thanks a lot for your help. Problem now sorted.