Received: from hotmail.com (f33.law7.hotmail.com [216.33.237.33]) by hub.org (8.9.3/8.9.3) with SMTP id IAA19480 for ; Mon, 10 Jan 2000 08:16:10 -0500 (EST) (envelope-from albertsql@hotmail.com) Received: (qmail 77867 invoked by uid 0); 10 Jan 2000 13:15:39 -0000 Message-ID: <20000110131539.77866.qmail@hotmail.com> Received: from 202.188.58.84 by www.hotmail.com with HTTP; Mon, 10 Jan 2000 05:15:39 PST X-Originating-IP: [202.188.58.84] From: "Albert Loo" To: pgsql-sql@postgresql.org Subject: query to do a backward 'skip', possible with (index) optimization ? Date: Mon, 10 Jan 2000 21:15:39 MYT Mime-Version: 1.0 Content-Type: text/plain; format=flowed hi, Coming from xbase background and having just started on sql, I tried sql queries that does xbase 'skip'. I wanted to implement record browsing with 'Next' and 'Previous' features. Currently, I'm testing this with a table of about 20,000 rows using psql. I'm able to perform 'forward skip' query with : => select * from mytable where id > '12345' order by id limit 1; - which returned what I wanted, 1 record with id value '12346'. Indexing on id further optimised the query. However, I'm unable to optimise query when doing a 'backward skip'. I've tried : => select * from mytable where id < '12345' order by id desc limit 1; - which returned the correct result '12344', but without optimization. I was hoping a descending order index key would optimise it, but I think postgresql don't support it (yet ?). Next, I tried using offset -1...which is not possible either :) => select * from mytable where id > '12345' order by id limit 1 offset -1; ERROR: parser: parse error at or near "-" Has anyone tried 'backward skip' with optimization ? ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com