Oracle question I have not been able to Google

I am making a web app where queries may return 1000+ records. I want to do something very common--divide the results into pages.

in other words I want LIMIT 100, 100 or something equivalent in oracle.

I have seen this question asked in this forum and responsed have been to google for ROW_NUMBER().

however I have not found an example on Google that does exactly what i want. I have tried

SELECT foo, ROW_NUMBER() OVER (ORDER BY bar) AS num FROM bar WHERE num > 99 AND num < 200

which of course hasn't worked.
--------------------------------
ok I found this in another post:

select table_name, n from (
select table_name, rownum n from (
select table_name from all_tables
order by table_name)
) where n >= 2 and n <= 4


I am somewhat new to DB's and am confused about table_name and all_tables.

lets say I have a table called 'foo', i want to select one column called 'bar', and I want rows >= 2 and <= 4.

also, would the above query be slow? because it looks like you are doing 3 queries.

Something like:

should do the trick.

Pablo- I see you and Scott/Tiger are best buddies.

Personally, I never use those examples unless someobody is working in that context already.

Personally I always try to give examples, with data available to everyone, so -> scott/tiger (or EMPLOYEE.FDB in Firebird, or Northwind in MsSQL).
I try be more specific only if someone gives me a sql script to generate and populate structures and this happens once in a thousand posts.

thanks pabloj, but i have a question.

one of my concerns is performance. i have 1000+ records, and I want to page through them 100 at a time for performance reasons.

would using your query help me in performance? because it seems like the subquery



would select all 1000 records, then the outer query would filter through this.

Maybe its a stupid mistake on my part... or maybe i am overlooking something .... could someone tell me what am i doing wrong here ....
this query
select count(*) from table_a where rownum = 1 ;
returns me 1..
but when i give
select count(*) form table_a where rownum = 2000;
returns me 0 (i have 3600 rows in the table incase someone tought on those lines)
neither does this work
select count(*) from table_a where rownum > 1 and rownum < 200;

Could someone tell me what i am doing wrong here?

rownum is an artificial column generated AFTER rows are selected.
You are effectively saying
'Take out the second marble from the box but not the first.' But you can't take out the second marble until you have taken out the first.

You can try
select * from
(select x.*,rownum rn from x)
where rn > 1000