X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 247ED52800 for ; Wed, 18 May 2005 14:54:02 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20558-01 for ; Wed, 18 May 2005 17:53:58 +0000 (GMT) Received: from quasar.skima.is (quasar.skima.is [212.30.200.205]) by svr1.postgresql.org (Postfix) with ESMTP id 10605528AB for ; Wed, 18 May 2005 14:53:55 -0300 (ADT) Received: from gnari ([85.220.28.44] [85.220.28.44]) by quasar.skima.is with ESMTP; Wed, 18 May 2005 17:53:54 Z Subject: Re: Changed to: how to solve the get next 100 records problem From: Ragnar =?ISO-8859-1?Q?Hafsta=F0?= To: Alain Cc: Sql-Postgre , Diogenes In-Reply-To: <428B6263.5050402@pobox.com> References: <20050517170636.GC12405@phlogiston.dyndns.org> <428A2E81.2060501@pobox.com> <1116353539.26520.5.camel@localhost.localdomain> <428A3B54.4070902@pobox.com> <1116367382.9362.11.camel@localhost.localdomain> <428AA569.3010708@pobox.com> <1116404906.9362.19.camel@localhost.localdomain> <428B6263.5050402@pobox.com> Content-Type: text/plain Date: Wed, 18 May 2005 17:53:52 +0000 Message-Id: <1116438832.8187.15.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.2.1.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/153 X-Sequence-Number: 21466 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