Subject Re: [firebird-support] Digest Number 2473
Author Dan Wilson
On 7/21/2004 at 7:45 AM Stan wrote:

> In the IBObjects group (I am posting in this group rather than IBObjects
> because my question is "wider" than just IBO issues) Helen recently wrote:
> "Always use a parameterised WHERE clause to limit the output to 200 rows
> or
> less."
> This, of course, is good advice. I would like to know how to implement
> this
> limitation when the result set is controlled by the user. I'm thinking of
> the (common, I suppose) case where the user types a few characters (of,
> say,
> a product or a last name) and clicks a Search button, the intention being
> to
> provide the user with a list they can scroll through to select their
> desired
> item or person. Even if the software requires the typing of more than
> just
> one or two letters before searching, it can still be the case that the
> resulting search will contain more than 200 items. What techniques or
> components might be suggested to limit the response set to 200 items while
> still allowing the user to seamlessly scroll through their intended result
> set?
> TIA,
> -- Stan

You could try using FIRST n SKIP m:

for the first query use: SELECT FIRST 200 * FROM wherever WHERE whatever.
for the second query use: SELECT FIRST 200 SKIP 200 * FROM wherever WHERE whatever.
for the third query use: SELECT FIRST 200 SKIP 400 * FROM wherever WHERE whatever.

Depending, of course, on how expensive in terms of time each query is: if it takes 10 minutes to return each result set, this may not be a practical solution.