From: Steve Crawford <scrawford@pinpointresearch.com> To: Francisco Leovey <fleovey@yahoo.com> Cc: "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org> Sent: Tuesday, May 15, 2012 12:49 PM Subject: Re: [NOVICE] How to select by proximity
On 05/15/2012 04:30 AM, Francisco Leovey wrote:
Can OFFSET be a negative number?
What I was looking for is a way to access the +1 and -1 record with SQL as seen on the screen when you do a select using an index or a sort.
With PgAdmin when you see a record of interest in the middle of the display, you can visually see the one above and below.
I seem to remember that in Informix I had a go + or - inside a pointer loop.
Depending on your use-case you might be able to select the current and surrounding records like this:
with prev as (select * from yourtable where yourcolumn <= 'Marriot' order by yourcolumn desc limit 6), next as (select * from yourtable where yourcolumn > 'Marriot' order by yourcolumn limit 5), select * from prev union select * from next order by yourcolumn ;
But if "yourcolumn" is not unique, your results may be unpredictable.
