/*
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
Ok, first...does anyone have the source code for altavista? ;-)
I've been struggling with a logic problem at many client projects.
Lets say, for a web page, a user of the system selects a page that lists
all items for sale in our company catalog. Lets assume the list of items
is qty 1 million. Lets also assume (while filtering options are
available), the user currently is browsing the entire list, rather than
shortening the rowset by some pre-defined filter.
Obviously, I would like to present the user with a page (similar to
search engines) that allows them to select an option of viewing 25 items
per page, and flip through the items page by page, while sorting by a
user selected column.
This issues I've had are:
1. Since a web page is stateless, how do a select the first 25 records
from a database, then the next 25...etc, all based on the choice of sort
column. With indexes this is fairly simple, but i hate to index 30
columns in a table, many of which are varchars.
2. ADO Provides this ability, but also grabs the entire recordset. (A
Bad thing when pumping data across an n-tier system to a user through a
56k dialup).
3. If a user sits on one of the pages for 25 minutes, and new data is
entered by another user during that time, the next page, or subsequent
pages should reflect the changes and grab the current recordset.
I'm struggling with the methodology of how to walk through a very large
rowset from a web front-end while maintaining uniqueness of the results,
keeping the system stateless, and not having to return the entire rowset
for each page (only the 25 records viewed for that page, rather than the
1 million available records).
Does anyone have a detailed idea of how to accomplish this issue? What
technology is used? Temp tables, indexes, or any other features used to
provide this functionality.
by