Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Question

 
Butch Car
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • 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.
 
Daniel Dunleavy
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
select * from test_table where rownum > 150 and rownum < 201
 
Frank Carver
Sheriff
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
See, someone else got here before I did.
 
Butch Car
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, I'll give that a try today.
 
Mallika Kumar
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • 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
  • 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
  • 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
 
Laurent Leonard
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • 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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic