andrew gatt wrote:
Thanks for your reply, yes i can use the limit clause but it would help me
greatly if i could also get the offset out of the query. So i can then
calculate the next offset and limit. For example:
Name
Anna
Bob
Carl
Dan
Fred
the > 'C%' query gives:
Carl
Dan
Fred
which as you've pointed out can be limited, but i'd like to know that the
first result is the third row in the set, is this possible? Or do you have
to use the count statement?
Andrew,
If you really want the start offset you will have to use the count
function.
I think you can do what you want using the limit and offset clauses in
your SQL though. Set the offset to zero for the first set of names.
select name from people where name >= 'C%' limit 20 offset 0;
Then increase the offset by the limit for additional results.
select name from people where name >= 'C%' limit 20 offset 20;
select name from people where name >= 'C%' limit 20 offset 40;
If you are using prepared SQL statements you can bind values to the
limit and offset clauses to get the required names. Prepare this
statement once:
select name from people
where name >= (:start || '%') limit :limit offset :offset;
Then prior to each execution you can change the value bound to the
variables :start, :limit, and :offset (or just use the previous value if
it hasn't changed).
HTH
Dennis Cote
