Re: How to select by proximity

On 2012-05-14 14:00, Francisco Leovey wrote:
> Hello group
>
> I have a large table indexed by a varchar column
> I would like to select records that are around the hit value, say
> "Marriot" by index order
> How can I retrieve the prior and next in order?
> Is there a goto "Marriot" and then a goto +1 and goto -1 SQL 
> facility?

The LIMIT and OFFSET commands in a SELECT statement (in combination 
with ORDER BY) should be helpful here.

However, it's important to remember the the database has no real 
concept of 'index order': All data is unordered unless ask it to be 
ordered.  It's not going through a list until it gets to 'Marriot', and 
then returning that to you; it's returning a set which includes 
'Marriot'.  To do what you want, you'll likely need to return the 
*entire* table, and then pull out what parts you want.  The LIMIT and 
OFFSET would only be helpful once you know where 'Marriot' appears in 
the (ordered) result of your particular query at a particular point in 
time.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------