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

Reply via email to