You can do it with w/pure SQL server, but it can be pretty expensive
depending on the query.
The idea is to do:
select top 10
*
from
(
select top 40
*
from
table
order by
tableId asc
)
order by
tableId desc
You have to match up the where clauses exactly. The idea is the virtual
table grabs the current page number and add one then multiply by the number
of records to display (records*(page+1) = 10*(3+1).) This would pull out the
top 40 records. You then order by descending grabbing out just the number of
items on the page. This will grab out records 31-40.
Hope this helps!
-Dan
>-----Original Message-----
>From: Adam Churvis [mailto:[EMAIL PROTECTED]
>Sent: Monday, March 27, 2006 2:52 PM
>To: CF-Talk
>Subject: Re: DB Pagination Question
>
>That's too bad. With SQL Server 2005 you can do this:
>
>With Books AS
>(select
> isbn,
> title,
> ROW_NUMBER() OVER(order by Title) as RowNumber
>from
> book)
>select
> *
>from
> Books
>where
> RowNumber between 2 and 14
>
>....and then just use a simple calculation for the starting and ending row
>numbers of the page you want to retrieve.
>
>This is how we perform efficient custom pagination for ASP.NET 2.0 GridView
>controls that use object data sources.
>
>The equivalent SQL Server 2000 techniques I've seen all populate temporary
>tables with the *entire* contents of the target table, then select only
>those needed from the temp table. Try *that* under load!
>
>Respectfully,
>
>Adam Phillip Churvis
>Certified Advanced ColdFusion MX 7 Developer
>BlueDragon Alliance Founding Committee
>
>
>
>Get advanced intensive Master-level training in
>C# & ASP.NET 2.0 for ColdFusion Developers at
>ProductivityEnhancement.com
>
> ----- Original Message -----
> From: Jim Curran
> To: CF-Talk
> Sent: Monday, March 27, 2006 1:27 PM
> Subject: RE: DB Pagination Question
>
>
> No,
>
> MSSQL 2000....
>
> -----Original Message-----
> From: Adam Churvis [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 27, 2006 1:14 PM
> To: CF-Talk
> Subject: Re: DB Pagination Question
>
> Are you using SQL Server 2005?
> Respectfully,
>
> Adam Phillip Churvis
> Certified Advanced ColdFusion MX 7 Developer BlueDragon Alliance Founding
> Committee
>
>
>
> Get advanced intensive Master-level training in C# & ASP.NET 2.0 for
> ColdFusion Developers at ProductivityEnhancement.com
>
> ----- Original Message -----
> From: Jim Curran
> To: CF-Talk
> Sent: Monday, March 27, 2006 10:45 AM
> Subject: DB Pagination Question
>
>
> Hi All,
>
> I am wondering what the best way to handle the following situation is.
>I
> have a database with approx 100,000 records. Users can search on these
> records with multiple criteria. Some result sets can contain 10,000
> records
> or more. Also, there is a possibility of sorting the results. Using
>CF &
> SQL server, what is the most efficient way to paginate through the
> results,
> without hogging memory or re-running the entire query for each page
>view?
>
> TIA.
>
> - jim
>
>
>
>
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236271
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
