Skip site navigation (1) Skip section navigation (2)

Re: How to select by proximity

From: Francisco Leovey <fleovey(at)yahoo(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: How to select by proximity
Date: 2012-05-15 17:51:56
Message-ID: 1337104316.9861.YahooMailNeo@web39305.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
That works fine, thank you!

 

________________________________
 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> 
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.

Cheers,
Steve

In response to

pgsql-novice by date

Next:From: Ilija VidoevskiDate: 2012-05-16 11:43:19
Subject: remote connection problem
Previous:From: Alejandro BarraganDate: 2012-05-15 17:07:36
Subject: Console Output

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group