Re: Changed to: how to solve the get next 100 records problem

On Wed, 2005-05-18 at 12:42 -0300, Alain wrote:
> I found something that is both fast and simple (program side):
> ...
> subsequent selects are
> (select ... from tab WHERE skey=skey_last AND pkey>pkey_last
> ORDER BY skey,pkey LIMIT 100)
> UNION
> (select ... from tab WHERE skey>skey_last
> ORDER BY skey,pkey LIMIT 100)
> ORDER BY skey,pkey LIMIT 100;
> ...
> The only strange thing is that without the 3rd order by, the order is
> wrong. I didn't expect it because each select is created ordered. Is it
> expected that UNION mixes it all up? (using postgre 7.4.1)

ORDER BY on subselects are not garanteed by SQL to have any effect,
but Postgres tends to do what you want when possible and not
detrimental to performance.
In this case, Postgres would probably have kept the order had
you used UNION ALL

a plain UNION implies DISTINCT, which Postgres is free to
implement any way it wants, possibly destroying the order

in this case a UNION ALL is appropriate, as you know that
the 2 selects do not overlap. possibly, a future version
of the planner will be able to detect this.

in any case, the last ORDER BY LIMIT does not cost much, and it
protects you against implementation changes, and limits
the result to 100 records, which might be what you want.

> Please comment on this. I tested and it worked but I really new to sql
> and I feel insecure...

it's good.

gnari