Re: 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.

Cheers,
Steve