File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQL Question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Question" Watch "SQL Question" New topic
Author

SQL Question

Butch Car
Greenhorn

Joined: Jan 12, 2001
Posts: 13
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
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
select * from test_table where rownum > 150 and rownum < 201
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
See, someone else got here before I did.


Read about me at frankcarver.me ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
Butch Car
Greenhorn

Joined: Jan 12, 2001
Posts: 13
Thanks, I'll give that a try today.
Mallika Kumar
Ranch Hand

Joined: Feb 15, 2001
Posts: 61
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

Joined: Mar 13, 2001
Posts: 276
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

Joined: Jan 12, 2001
Posts: 13
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
Ranch Hand

Joined: May 15, 2001
Posts: 35
What about this ?
Select col1, col2 from
(select rownum myrownum, col1, col2 from mytable)
where myrownum>100 and myrownum<150;
Laurent


Laurent LEONARD
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Question
 
Similar Threads
JSP with Oracle / SQL
can i run HQL in any console and see result
JSP and EJB
Techniques For Paging/Caching Search Results
Please help me to convert this SQL to hibernate query