Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA48625 for ; Mon, 10 Jan 2000 10:17:52 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id KAA03117; Mon, 10 Jan 2000 10:17:49 -0500 (EST) To: "Albert Loo" cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] query to do a backward 'skip', possible with (index) optimization ? In-reply-to: <20000110131539.77866.qmail@hotmail.com> References: <20000110131539.77866.qmail@hotmail.com> Comments: In-reply-to "Albert Loo" message dated "Mon, 10 Jan 2000 21:15:39 +0700" Date: Mon, 10 Jan 2000 10:17:49 -0500 Message-ID: <3114.947517469@sss.pgh.pa.us> From: Tom Lane "Albert Loo" writes: > => select * from mytable where id < '12345' order by id desc limit 1; > - which returned the correct result '12344', but without optimization. 6.5.* doesn't know how to do that using an index (but 7.0 will). I seem to recall that someone posted a patch that partially solved the problem in 6.5.* sources; check the archives if you are in a hurry. BTW, I think you're probably going at this the wrong way, because it doesn't extend to the case of fetching the *next* record in a prespecified query. You probably want to use DECLARE CURSOR and FETCH. regards, tom lane