Search results across multiple pages

Search results across multiple pages

Post by karim ami » Thu, 10 May 2001 04:25:22



I am trying to implement something similar to what search engines do.  You
type in a search phrase and if the results are too many to list on one page,
they get broken up across multiple pages that the user can traverse.  How is
this done.  How do they return just the first 20 records and on the next
page return the next 20 after that?  Can someone shed some light on this
please?  Thanks.
 
 
 

Search results across multiple pages

Post by Sara » Thu, 10 May 2001 05:10:45


Karim,

Please refer to this example......

---------------------------------
Range of Rows from resultset.
Paging a resultset.

If you're doing forward and backward paging of dynamic data, I recommend
using a unique row key value instead of a page number or row number.  Make
the client remember the unique key value of the first and last rows of the
current page.  The unique key value of the first row is used to get the
previous page and the unique key value of the last row is used to get the
next page.

New and lost rows will be handled much better.  Performance should be much
better.  The client just has to handle the situation of getting fewer rows
than expected when it reaches the first row in the table.

Stored procedures handle the situation nicely.  Pass NULL for the parameter
to get the first or last page.
*/


AS
    --return first page if parameters are null.

        SELECT TOP 10 *
        FROM my_big_table
        ORDER BY unique_key
    ELSE
        SELECT TOP 10 *
        FROM my_big_table

        ORDER BY unique_key


AS
    --return last page if parameters are null.

        SELECT *
        FROM
            (
            SELECT TOP 10 *
            FROM my_big_table
            ORDER BY unique_key DESC
            ) AS Reorder
        ORDER BY unique_key
    ELSE
        SELECT *
        FROM
            (
            SELECT TOP 10 *
            FROM my_big_table

            ORDER BY unique_key DESC
            ) AS Reorder
        ORDER BY unique_key

/*
The above routines assume that the resultset is ordered by the unique key.
If that's not true, a combination of a sort column and the unique key
can be used. Pass the sort column value as well as the unique key to the
next_page and previous_page procedures.  Make sure the table has an index
on the combination of the sort column and the unqiue key.
*/

CREATE PROCEDURE next_page


AS
    --return first page if parameters are null.

        SELECT TOP 10 *
        FROM my_big_table
        ORDER BY sort_column, unique_key
    ELSE
        SELECT TOP 10 *
        FROM my_big_table
        WHERE

            and (


                )
        ORDER BY sort_column, unique_key

CREATE PROCEDURE previous_page


AS
    --return last page if parameters are null.

        SELECT *
        FROM
            (
            SELECT TOP 10 *
            FROM my_big_table
            ORDER BY sort_column DESC, unique_key DESC
            ) AS Reorder
        ORDER BY unique_key
    ELSE
        SELECT *
        FROM
            (
            SELECT TOP 10 *
            FROM my_big_table
            WHERE

                and (


                    )
            ORDER BY sort_column DESC, unique_key DESC
            ) AS Reorder
        ORDER BY sort_column, unique_key

---------------------------------

( courtesy:Don )

hth
Sara.