• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

SQL Question

 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
select * from test_table where rownum > 150 and rownum < 201
 
Sheriff
Posts: 7001
6
Eclipse IDE Python C++ Debian Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
See, someone else got here before I did.
 
Butch Car
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks, I'll give that a try today.
 
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your right.
You should use a unique identifier field on the record anyway, and then do uid > 150 and uid < 201.
Dan
 
Butch Car
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What about this ?
Select col1, col2 from
(select rownum myrownum, col1, col2 from mytable)
where myrownum>100 and myrownum<150;
Laurent
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic