It's easy in SQL 2005, and very scalable for large rowsets:
WITH PagedResultSet AS (
SELECT
ROW_NUMBER() OVER(ORDER BY LastName ASC, FirstName ASC) AS
RowNumber,
UserID,
Email,
LastName,
FirstName
FROM
ApplicationUser
)
SELECT RowNumber, UserID, Email, LastName + ', ' + FirstName AS FullName
FROM PagedResultSet
WHERE RowNumber BETWEEN
<cfqueryparam value="#rangeStart#" cfsqltype="CF_SQL_INTEGER"> AND
<cfqueryparam value="#rangeEnd#" cfsqltype="CF_SQL_INTEGER">
ORDER BY LastName ASC, FirstName ASC
This is *much* better than retrieving everything and letting it sit in
memory for a number of reasons.
Hope this helps.
Respectfully,
Adam Phillip Churvis
President
Productivity Enhancement
> -----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:295729
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
