Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: What's the popular techniques to implement search result <<Previous, Next>>?

Re: What's the popular techniques to implement search result <<Previous, Next>>?

From: Steve Halko <steveh_at_redshift.com>
Date: 2000/06/16
Message-ID: <skm1m2i0is482@corp.supernews.com>#1/1

You cannot use ROWNUM as someone else suggested, because ROWNUM is assigned after the row is returned to you. So the first row returned to you is always ROWNUM = 1.

So for example this query will always return no rows at all:

select * from <table> where ROWNUM = 2

So if you only want to retrieve 10 rows from a table, they will always have ROWNUMs from 1 to 10, no matter which 10 rows you retrieve. This means of course if you use WHERE ROWNUM between 11 AND 20, you'll never get any rows back.

The way we do it is to run the original query and store the results, along with a sequential number for each record in a temporary table. Then you can display whichever set of 10 rows you want by querying against the sequential number. When the user clicks on NEXT, you will of course need to know which set of 10 records he is currently looking at. This is done by storing the sequential record number in a hidden HTML field. For example, if the hidden field on your HTML form is named FIRST_REC, then when the user hits the NEXT button, your query to fetch the next 10 records would look something like:

select * from temporary_table where sequential_number between FIRST_REC +10 and FIRST_REC + 19;

"Jack Liu" <jack.liu_at_visual-x.com> wrote in message news:8i85lu$ibb_at_chronicle.concentric.net...
> Hi,
>
> This may be a general topic question discussed before. But please help.
>
> I am looking for efficient techniques for this problem:
> When conducting a database search, it is possible to return thousands
> of results. Mostly likely the web page only displays the first 10
> results,
> and provides link "Next" to the next 10 results. Then if the user clicks
>
> "Next", the database query should be able to efficiently skip the first
> 10 and reach 11-20 of the search results. What's the common
> techniques for this problem. I am kind of new to advanced Oracle
> features.
>
> Thanks,
>
> Jack
>
>
>
>
Received on Fri Jun 16 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US