From: | Steve Crawford <scrawford(at)pinpointresearch(dot)com> |
---|---|
To: | Francisco Leovey <fleovey(at)yahoo(dot)com> |
Cc: | "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: How to select by proximity |
Date: | 2012-05-15 15:49:36 |
Message-ID: | 4FB27B10.5010802@pinpointresearch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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.
Cheers,
Steve
From | Date | Subject | |
---|---|---|---|
Next Message | Alejandro Barragan | 2012-05-15 17:07:36 | Console Output |
Previous Message | Francisco Leovey | 2012-05-15 11:30:24 | Re: How to select by proximity |