Jonathon, Here's a stored procedure you can use in MSSQL 2k for pagination:
http://blog.pengoworks.com/index.cfm/2006/6/19/MSSQL-2k-Stored-Procedure-for -Pagination It doesn't use #temp tables and it will only return the pages of data you're after. I've used it on tables with several millions of records and had great success with it. Also, in SQL Server 2005 you can solve this problem with Common Table Expressions (CTE) -Dan >-----Original Message----- >From: Jonathon Stierman [mailto:[EMAIL PROTECTED] >Sent: Wednesday, January 02, 2008 6:01 PM >To: CF-Talk >Subject: mssql pagination > >I can't imagine I'm the first person to try to do something like this, so >hopefully you guys can give me some insight! I know this isn't strictly >CF-related -- but I'm hoping some of you have tried to do something like >this in the past. > >I'm trying to produce a paginated list of records. The query I'm working >with has about 20000 rows, and I'd rather not force the user to sift >through >all those pages in a single page request. So I'd like to break it up, >maybe >display 50 records per page. I'd also prefer not to have the full query >sitting in memory every request. If I'm only displaying rows 50-100, I >only >want rows 50-100 in the query object. Using cfloop with startrow and >maxrows won't cut it this time. > >Apparently this is super easy in MYSQL using the LIMIT (records), (offset) >clause. > >Turns in MSSQL makes things difficult. A couple sites I hit up showed >examples of using of subqueries and the TOP filter, or (ugh) temporary >tables. I went with the subquery/TOP approach. For example, ordering by >dateAdded and wanting to show rows 20-30: > >SELECT TOP 10 sampleID >FROM Samples >WHERE 0=0 >AND sampleID NOT IN ( > SELECT TOP 20 sampleID > FROM Samples > ORDER BY Samples.dateAdded >) >ORDER BY Samples.dateAdded > >"Grab the first 10 records after eliminating the first 20 records, always >sorting by dateAdded." > >But I found it worked sporadically. Pages 1 and 2 worked fine, displaying >the 1-10 and 11-20 records respectively. But page 3 gave me some strange >results. It showed records 14-23 or some other mismatch. Obviously not >what I was looking for! > >After some investigation, I found out my subquery was not returning the >right samples to exlude. What I haven't been able to figure out is *why* >they are not returning the right subset. As far as I can tell, the SQL >looks good to me. > >So my question to you guys is -- > >1. Can you see anything wrong with how I've set up my Query? >2. How have you dealt with pagination in the past? Is there an easier >route than what I'm taking now? > >PS -- I am running on MSSQL Server 2000 (I have heard they offer an OFFSET >clause in 2005, but sadly, I do not have access to that). > >If anyone is interested in seeing some sample data of what I'm working on, >I >put up this HTML page: > >http://www.vimm.com/developerTools/sampledata.html > >You can see in the "All Samples" section that records 20-30 should be: >[147, >148, 149, 150, 63, 36, 40, 382, 383, 384]. >But the Total Wrapper query returns: [146, 147, 148, 149, 150, 63, 382, >383, >384, 440]. >What happened to record 40? It's row #27 in the All Samples query, but >it's >included in the exclude Nested Subquery that should only be pulling the top >20 results. How does that happen? > >Jonathon > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295736 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
