sql query to display search engine paging results

sql query to display search engine paging results

Post by robert_b_al.. » Thu, 18 Jan 2001 05:24:26



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/

 
 
 

sql query to display search engine paging results

Post by barry.. » Thu, 18 Jan 2001 06:13:33


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.



> 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/

 
 
 

sql query to display search engine paging results

Post by Spence » Thu, 18 Jan 2001 13:05:53


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.


> 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.



> > 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/

 
 
 

1. limit search result display on web page??

Hi people,

we are currently building an ASP/VBScript web page that searches records in
an Access DB using ODBC.

Problem: the number of hits can be several 100 or more, so we need to use
some "25-at-a-time" mechanism.

Constraints:
1. Using session variables to hold the data would flood our server's memory
(just pointers to files or records could be ok.)
2. the solution should be simpler than implementing a full web crawler

So far we intend to count all hits, then have the browser display a
confirmation dialog with the total number of hits. If accepted, we would
send the data over, displaying another confirmation dialog in the client
browser every 25 records.

Better ideas, pointers, code samples, etc would be GREATLY appreciated!!!
Thanks a lot!!!

*************************
please reply by email
*************************

Regards,
Marc Esser

2. rename columns?

3. How do I display search results across pages???

4. looking for SQL Server 7.0 Log Shipping on BORK 4.5, BO 4.5, MSDN Universal

5. Help needed optimizing display of search results in a web page

6. Oracle and Firewalls

7. Display links to other pages in web search results

8. Displaying multiple pages of search results

9. displaying search results in a page with frames

10. SQL for search page results

11. SQL Query for a search engine

12. Simple Search Engine SQL Query Question