Assuming you can do *TOP* within Oracle (i don't use it, so i don't know)
and there is *any* field you can order by, the following works great for me
(for SQL Server):
select top X item_ID
from attorney
where attorney_ID
in (
select top Y attorney_ID
from attorney
order by attorney_ID DESC
)
order by attorney_ID
where x = total number of rows you want
and y = total_records - start_row
if i have 1000 rows and want records 1 - 10
x = 10
y = 1000 - 1 + 1 = 1000
if i want 401 - 420
x = 20
y = 1000 - 401 + 1 = 600
Hope that helps.
- j
jim curran
technical director
nylon technology
212.691.1134
[EMAIL PROTECTED]
-----Original Message-----
From: Edwards Robert (air0rae) [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 28, 2002 9:43 AM
To: CF-Talk
Subject: Slightly OT: CFQuery, Oracle and Limiting rows
I have a query that returns about 5000+ rows of data. Currently it takes
about 51 seconds to execute (it's a very complex query). I'm only
displaying 100 records per page using start and end rows in a cfloop. The
problem is that the query gets executed every time I go to a page and it
take 50 seconds or so each time. I know I can use MAXROWS in my cfquery tag
to limit the return to 100 rows, but that will only get me the first 100.
Does anyone know how to set it up so I can get the second 100, or third or
fortieth? I would guess this needs to be done in oracle instead of CF, but
there may be a custom tag I don't know about. (About the data: There is no
iterator or counter on the rows so I cannot do a select where rowid between
1 and 100 or 101 and 200 and so on. Each row has a unique value in it, and
can be added to or deleted from at any time. Also, the data needs to be
real-time so caching a query is out of the question as well.)
Any suggestions would be greatly appreciated. My phone is ringing off the
hook with complaints of the slow response.
Thanks,
Rob Edwards Phone: (502) 359-1627
Systems Management Tools Pager: (502) 478-1116
United Parcel Service Fax: (502) 359-0094
EMail: [EMAIL PROTECTED]
(2B || !2B) == ?
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
