SQL Question

[Butch Car]

Excerpt from Frank Carver post:
For bigger datasets or busier sites it has really got to be
the "go back to the database" approach. It works much better
if you can ask the database to only retrieve (say) items 151
to 200, but not all databases support that sort of SQL.
We use this approach here with Oracle and it works very
well. In effect we are amortizing the cost of the query
across several requests.
Question:
Can you provide a simple example of what an Oracle query
would look like which returned items 151 through 200.

[Daniel Dunleavy]

select * from test_table where rownum > 150 and rownum < 201

[Frank Carver]

See, someone else got here before I did.

[Butch Car]

Thanks, I'll give that a try today.

[Mallika Kumar]

Hi,
I tried using a similar select statement in Oracle 8i, but it does not work.
Example: SELECT EMPNAME FROM EMPLOYESS WHERE ROWNUM < 11;<br /> Gives first 10 rows.<br /> SELECT EMPNAME FROM EMPLOYESS WHERE ROWNUM > 11 AND ROWNUM < 21;
no rows selected.
This is because rownum value is a post-query value, ie, our query should first reach that particular row, only then can you access rows with ROWNUM.
Correct me if I'm wrong. I could not use the second select statement in my query.
Thanks.

[Daniel Dunleavy]

Your right.
You should use a unique identifier field on the record anyway, and then do uid > 150 and uid < 201.
Dan

[Butch Car]

What exactly is meant by "unique identifier field on the record".
That must be something that's generated by the query and
not an actual field in the database.

Do you have a simple example query?
TIA,
BC

[Laurent Leonard]

What about this ?
Select col1, col2 from
(select rownum myrownum, col1, col2 from mytable)
where myrownum>100 and myrownum<150;
Laurent