Re: sql query to display search engine paging results

the "inline" order by is NOT available in Oracle 8.0.x (as you are obviously aware). this feature (finally!) appeared in one of the 8.1.x releases.

one approach to solving the problem is to order by a single expression which returns unique values.

for example:

select lastname, firstname, expr3
from mytable
where lastname like 'ALLEN%'
order by rpad(lastname,30)||rpad(firstname,30)||id

fetch rows until notfound or until you've fetched and processed 75 rows. you'll need to save the value of the order by expression from the last row fetched, for use in a subsequent query:

select lastname, firstname, expr3
from mytable
where lastname like 'ALLEN%'
and rpad(lastname,30)||rpad(firstname,30)||key > :saved_expr order by rpad(lastname,30)||rpad(firstname,30)||id

fetch rows until notfound or until 75 rows are processed, and again, save the value of the order by expression from the last row fetched, repeat...

i've found this to be a workable solution in some cases.

if you don't want to create a unique expression to order by, then you can resort to another (less elegant) approach: re-running the original query (crossing your fingers that it will return rows in the same order it did last time), fetching and tossing rows that were previously fetched and processed, then fetching another 75 rows for display. in this approach, you'll need to save a rowcount value to know how many rows to toss on each subsequent execution, though you run the risk of either skipping or duplicating a value if the contents of the table changes between queries, or if the result set is returned in a different order.

another approach is to fetch the entire result set on the first execution, and "cache" the result set on the middle tier server, and not have to go back to the database for the subsequent requests.

HTH if anyone has experience with a more elegant (or less resource intensive) approaches to solving this problem with Oracle 8.0.6, i would appreciate hearing about it.

NOTE: the "inline" order by syntax is NOT available in 8.0.6.

<barryday_at_my-deja.com> wrote in message news:942dhm$cd6$1_at_nnrp1.deja.com...
> Hi,
> I would be very interested in any answers you get to this post. Check
> out my post below (about 17 items down) from barryday. Sounds like I
> have the exact same problem that you have. If you get any good
> feedback, please forward them on and I'll do the same for you. Thanks.
> bday_at_autoprof.com
>
> In article <942alf$9qp$1_at_nnrp1.deja.com>,
> robert_b_allen_at_my-deja.com wrote:
> > Hi,
> >
> > We are having a problem finding the right query to return the next
> > resultset of say 75 when querying on last, first, and middle names.
> > There are say 300 people with lastname like 'ALLEN%' and we want to
> > show 75 records per page. Rownum doesn't group them by
> > lastname,firstname so that doesn't work. Also if we know the 75'th
> > record is say John M. Allen and we do a > , then it will not return
> > Amanda Allen-Smith becuase Allen-Smith is like 'ALLEN%' but Amanda is
 <
> > John. Do you understand my problem. We could build temporary views
> > but that would require a lot of work and a lot of memory. Is there
 any
> > way to do this with Oracle8 not 8i?
> >
> > Thanks for your help.
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
>
> Sent via Deja.com
> http://www.deja.com/
>
Received on Tue Jan 16 2001 - 22:05:53 CST