Обсуждение: How to select by proximity
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?
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. ---------------------------------------------------------------
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.
From: Postgres <DStaal@usa.net>
To: pgsql-novice@postgresql.org
Sent: Monday, May 14, 2012 5:24 PM
Subject: Re: [NOVICE] How to select by proximity
To: pgsql-novice@postgresql.org
Sent: Monday, May 14, 2012 5:24 PM
Subject: Re: [NOVICE] 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.
---------------------------------------------------------------
-- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
On 05/15/2012 04:30 AM, Francisco Leovey wrote:
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
Depending on your use-case you might be able to select the current and surrounding records like this: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.
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
That works fine, thank you!
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
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:
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
Depending on your use-case you might be able to select the current and surrounding records like this: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.
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