There are a number of ways to do this. The typical way that I get this done is
(im going to use your example of rows 31-50 for a total of 20 rows)
SELECT TOP 20 *
FROM [tableName]
WHERE [primaryKeyField] not in (
SELECT top 30 [primaryKeyField]
FROM [tableName]
WHERE [criteriaField] = 'value'
ORDER BY [sortField]
)
AND ([criteriaField] = 'value')
ORDER BY [sortField]
>After all this time I should know this one but, well... I don't.
>
>How does one go about selecting a specific set of rows from an SQL 2000
>database Query? So as not to confuse the issue let me explain further.
>
>The client has a database table of over 500,000 records and wants to specify
>the query and sort criteria then return a specific set or rows; say rows 800
>thru 850 or rows 30-50. Depending on the selection criteria there could be
>any number of matches up to 500,000. There are no sequentially number
>fields. I really don't want to pull all 500,00 records into memory.
>
>Example:
>
>Select {stuff}
>From {table}
>Where (something = selections)
>Order By Name
>
>However I only want to retrieve rows 30-50. I assume I will first need to
>perform a count using the same criteria to see if those rows exist but then
>how would I go about retrieving just them if they did?
>
>
>
>Best Regards,
>
>Dennis Powers
>UXB Internet - A website design and Hosting Company
>690 Wolcott Road
>P.O. Box 6029
>Wolcott, CT 06716
>Tel: (203)879-2844
>http://www.uxbinternet.com/
>http://www.uxb.net/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create Web Applications With ColdFusion MX7 & Flex 2.
Build powerful, scalable RIAs. Free Trial
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284162
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
