Range 201 - 250
SELECT TOP 50 your fields
FROM your tables
WHERE your joins and filters
AND PrimaryKey NOT IN (
SELECT TOP 200 your fields
FROM your tables
WHERE your joins and filters
ORDER BY your order
)
ORDER BY your order
Pascal Peters
Certified ColdFusion (5.0) Advanced Developer
Certified Web Developer
Macromedia Certified Instructor
LR Technologies, Belgium
Tel +32 2 639 68 70
Fax +32 2 639 68 99
Email [EMAIL PROTECTED]
Web www.lrt.be
-----Original Message-----
From: Steve Oliver [mailto:[EMAIL PROTECTED]]
Sent: donderdag 31 januari 2002 22:48
To: CF-Talk
Subject: MSSQL and LIMIT
I am doing a query on a rather large table, lets say there are 20000
rows, and I want to display them on a page, 50 rows at a time, starting
from row 200.
I know in MySQL I can just do a Limit 200,50. But MSSQL doesn't seem to
have anything like that. I tried a stored procedure to use a cursor,
but that returns 50 recordsets, instead of 50 rows in one recordset.
The way I see it done time and time again is to retrieve all records
with cfquery, then limit the output with cfoutput's startrow and
maxrows, or cfloop's startrow and endrow.
But that would cause the entire query to be retrieved each and
everytime, which seems just plain stupid to me.
The database is updated regularly, so cacheing isn't an option.
Does anyone else have a better way to do this?
______________________
steve oliver
atnet solutions, inc.
http://www.atnetsolutions.com
______________________________________________________________________
Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
