Retreive 90th -100th rows from a query

Ramanuj · November 30th, 2003, 11:20 AM

I have a set of tables with almost 2,00,000 records each.
I want to search for a particular condition using JOIN and
THEN retreive only 20th - 30th record; and not all.

I can use TOP 10 to get 1-10th record; but that doesn't
solve my purpose as I display the records in a Webpage
with 10 records each page; and when the user is in the 9th
page i got to display 90th - 100th record; and so on.

DONT want to use cursors.
Is there any hidden row returned by SQL 2000 which
contains Serial Number; i.e the record position which
helps me to achieve it.

Imar · November 30th, 2003, 12:13 PM

Hi there,

Take a look at this article: http://www.4guysfromrolla.com/webtech/062899-1.shtml

It describes custom paging using a Stored Procedure. The downside of the solution explained in this example is that a copy of the original data is made, every-time you call the procedure. No fun with 2,000,000 records ;) You could decide to create a permanent copy of the data, purely for your data navigation. Depending on the app and database you have, this may work pretty well or it may turn out to be a maintenance nightmare ;)

Take a look here: http://www.adopenstatic.com/experime...dsetpaging.asp for a discussion on various paging methods and their speed.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Jeff Mason · November 30th, 2003, 12:50 PM

Is there a column which orders the data? If so, you might be able to use a technique where you SET ROWCOUNT equal to the number of rows you want to return (e.g. 10), or use the TOP clause, then construct a WHERE clause which selects all rows whose ordering column value is greater than a parameter. Start with the parameter value less than the smallest legal ordering value. When you execute the procedure that displays the first set of rows, remember the ordering value of the last row you displayed, and use this as the parameter for the next fetch. Repeat as necessary.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Ramanuj · December 1st, 2003, 03:07 AM

Quote:

quote:Originally posted by Jeff Mason
Is there a column which orders the data? If so, you might be able to use a technique where you SET ROWCOUNT equal to the number of rows you want to return (e.g. 10), or use the TOP clause, then construct a WHERE clause which selects all rows whose ordering column value is greater than a parameter. Start with the parameter value less than the smallest legal ordering value. When you execute the procedure that displays the first set of rows, remember the ordering value of the last row you displayed, and use this as the parameter for the next fetch. Repeat as necessary.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thanks Jeff.
I have a small Problem. If the user goes to 10th page by clicking the hyperlink from 2nd page, my last row displayed value would not help !!!

cheers,
Ramanuj

Jeff Mason · December 1st, 2003, 07:49 AM

Yes. Allowing the user to 'bounce around' makes thing much more difficult. I was assuming just a forward paging movement through the data. Sorry.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com