Re: Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks

On Mon, Jun 30, 2008 at 8:51 AM, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:
>>
>> select * from foo where (o,pk)>(o,?) order by o limit 10;
>
> Hum, I think I must be missing something. I'm not sure why you're
> comparing 'o' to itself and you're not putting any ordering constraint
> on the primary key. I think the query should look closer to:
>
> SELECT * FROM foo WHERE (o,pk)>($1,$2) ORDER BY o,pk LIMIT 10;
>
> Or am I going mad?

yes, you are correct. you need to supply at least one value for each
ordered field. I think this is what the OP was tring to say.

usually it's much simpler than this:
select * from foo where pk > $1 order by pk limit 1;

This will pull up table in pk order which is usually fine. Any
ordering will do as long as the combination of fields being ordered
are unique. Adding pk as the second criteria is only needed if you
want to order by a non duplicate field. If 'o' is a candidate key
this is not required.

btw, the use of OFFSET for this type of problem is actually fairly
terrible...it's almost never a good idea.

merlin