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:295728 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4