Steve, You use the WHERE clause to drill. For example, if you order by an ident ity field, you could track the minimum and maximum value then where becomes
WHERE userid > #mySavedMaxId# First time through it would be "> 0" (top 50), then the next time through it would "> 50" (or some other number from that postion), next time through it would be " > 100" ... etc. Mark -----Original Message----- From: Steve Oliver [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 31, 2002 1:59 PM To: CF-Talk Subject: RE: MSSQL and LIMIT Um, that would just retrieve rows 1 through 50, I need something like rows 2000 through 2050 ______________________ steve oliver atnet solutions, inc. http://www.atnetsolutions.com -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 31, 2002 4:56 PM To: CF-Talk Subject: Re: MSSQL and LIMIT SELECT TOP 50 column FROM table ----- Original Message ----- From: Steve Oliver <[EMAIL PROTECTED]> Date: Thursday, January 31, 2002 2:48 pm Subject: MSSQL and LIMIT > I am doing a query on a rather large table, lets say there are 2000 0 > rows, and I want to display them on a page, 50 rows at a time, > startingfrom row 200. > > I know in MySQL I can just do a Limit 200,50. But MSSQL doesn't > seem to > have anything like that. I tried a stored procedure to use a curso r, > but that returns 50 recordsets, instead of 50 rows in one recordset . > > The way I see it done time and time again is to retrieve all records > with cfquery, then limit the output with cfoutput's startrow and > maxrows, or cfloop's startrow and endrow. > > But that would cause the entire query to be retrieved each and > everytime, which seems just plain stupid to me. > > The database is updated regularly, so cacheing isn't an option. > > Does anyone else have a better way to do this? > > ______________________ > steve oliver > atnet solutions, inc. > http://www.atnetsolutions.com > _______________________ _________________________ ______________________ > Dedicated Windows 2000 Server > PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER > Instant Activation . $99/Month . Free Setup > http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona > FAQ: http://www.thenetprofits.co.uk/coldfusion/faq > Archives: http://www.mail-archive.com/cf-talk@houseoffusion com/ > Unsubscribe: http://www.houseoffusion.com/index.cfm?sideb ar=lists > ______________________________________________________________________ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
