Thread: Selecting pairs of numbers
Hello all, I have an SQL problem which ought to be simple, but I can't get my head around it. I have pairs of integers - let's call them (x, y). In effect, x is a category, while y is an item within that category. For every x, there is always the same number of integers y; and both x and y are always numbered sequentially starting from 1. My problem is that I need to select a list of these pairs, ordered first on x and then on y, from a given starting point to a given finishing point and including all pairs in between. For example, I might have: x | y ----- 1 | 1 1 | 2 1 | 3 1 | 4 2 | 1 2 | 2 2 | 3 2 | 4 (etc) I then might want to extract a list from, say, (1, 3) to (3, 2), giving: x | y ----- 1 | 3 1 | 4 2 | 1 2 | 2 2 | 3 2 | 4 3 | 1 3 | 2 For the life of me, I can't figure out how to do this. Any help will be appreciated, or even just a pointer in the right direction. There's probably something simple that I'm just not seeing.... If anyone's interested, these numbers represent channels and pistons on the combination system of a largish pipe organ... it's for a hobby project. Many thanks in advance! Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
> I then might want to extract a list from, say, (1, 3) to (3, 2), giving: > > x | y > ----- > 1 | 3 > 1 | 4 > 2 | 1 > 2 | 2 > 2 | 3 > 2 | 4 > 3 | 1 > 3 | 2 > > For the life of me, I can't figure out how to do this. Hi, starting from this: chris=# select * from t order by x,y; x | y ---+--- 1 | 1 1 | 2 1 | 3 1 | 4 2 | 1 2 | 2 2 | 3 2 | 4 3 | 1 3 | 2 3 | 3 3 | 4 (12 rows) one trick that might help is this: chris=# select * from t where x*1000+y >= 1003 and x*1000+y <= 3002 order by x,y; x | y ---+--- 1 | 3 1 | 4 2 | 1 2 | 2 2 | 3 2 | 4 3 | 1 3 | 2 (8 rows) watch out, if you have y values bigger than 1000, though... Bye, chris.
On 10/05/2015 11:39 AM, Raymond O'Donnell wrote: > Hello all, > > I have an SQL problem which ought to be simple, but I can't get my head > around it. > > I have pairs of integers - let's call them (x, y). In effect, x is a > category, while y is an item within that category. For every x, there is > always the same number of integers y; and both x and y are always > numbered sequentially starting from 1. > > My problem is that I need to select a list of these pairs, ordered first > on x and then on y, from a given starting point to a given finishing > point and including all pairs in between. > > For example, I might have: > > x | y > ----- > 1 | 1 > 1 | 2 > 1 | 3 > 1 | 4 > 2 | 1 > 2 | 2 > 2 | 3 > 2 | 4 > (etc) > > I then might want to extract a list from, say, (1, 3) to (3, 2), giving: > > x | y > ----- > 1 | 3 > 1 | 4 > 2 | 1 > 2 | 2 > 2 | 3 > 2 | 4 > 3 | 1 > 3 | 2 > > For the life of me, I can't figure out how to do this. Any help will be > appreciated, or even just a pointer in the right direction. There's > probably something simple that I'm just not seeing.... aklaver@test=> create table pr_test(x int, y int); aklaver@test=> select * from pr_test where (x, y) between (1, 3) and (3,2) order by x,y; x | y ---+--- 1 | 3 1 | 4 2 | 1 2 | 2 2 | 3 2 | 4 3 | 1 3 | 2 > > If anyone's interested, these numbers represent channels and pistons on > the combination system of a largish pipe organ... it's for a hobby project. > > Many thanks in advance! > > Ray. > > -- Adrian Klaver adrian.klaver@aklaver.com
-----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Raymond O'Donnell Sent: Monday, October 05, 2015 2:40 PM To: 'PostgreSQL' <pgsql-general@postgresql.org> Subject: [GENERAL] Selecting pairs of numbers Hello all, I have an SQL problem which ought to be simple, but I can't get my head around it. I have pairs of integers - let's call them (x, y). In effect, x is a category, while y is an item within that category. Forevery x, there is always the same number of integers y; and both x and y are always numbered sequentially starting from1. My problem is that I need to select a list of these pairs, ordered first on x and then on y, from a given starting pointto a given finishing point and including all pairs in between. For example, I might have: x | y ----- 1 | 1 1 | 2 1 | 3 1 | 4 2 | 1 2 | 2 2 | 3 2 | 4 (etc) I then might want to extract a list from, say, (1, 3) to (3, 2), giving: x | y ----- 1 | 3 1 | 4 2 | 1 2 | 2 2 | 3 2 | 4 3 | 1 3 | 2 For the life of me, I can't figure out how to do this. Any help will be appreciated, or even just a pointer in the rightdirection. There's probably something simple that I'm just not seeing.... If anyone's interested, these numbers represent channels and pistons on the combination system of a largish pipe organ...it's for a hobby project. Many thanks in advance! Ray. ____________________________________ SELECT x, y FROM my_table WHERE (x*10 + y) >= (1*10 + 3) AND (x*10 + y) <= (3*10 + 2) ORDER BY x, y; Regards, Igor Neyman
On 05/10/2015 19:53, Adrian Klaver wrote: > On 10/05/2015 11:39 AM, Raymond O'Donnell wrote: >> Hello all, >> >> I have an SQL problem which ought to be simple, but I can't get my head >> around it. >> >> I have pairs of integers - let's call them (x, y). In effect, x is a >> category, while y is an item within that category. For every x, there is >> always the same number of integers y; and both x and y are always >> numbered sequentially starting from 1. >> >> My problem is that I need to select a list of these pairs, ordered first >> on x and then on y, from a given starting point to a given finishing >> point and including all pairs in between. >> >> For example, I might have: >> >> x | y >> ----- >> 1 | 1 >> 1 | 2 >> 1 | 3 >> 1 | 4 >> 2 | 1 >> 2 | 2 >> 2 | 3 >> 2 | 4 >> (etc) >> >> I then might want to extract a list from, say, (1, 3) to (3, 2), giving: >> >> x | y >> ----- >> 1 | 3 >> 1 | 4 >> 2 | 1 >> 2 | 2 >> 2 | 3 >> 2 | 4 >> 3 | 1 >> 3 | 2 >> >> For the life of me, I can't figure out how to do this. Any help will be >> appreciated, or even just a pointer in the right direction. There's >> probably something simple that I'm just not seeing.... > > aklaver@test=> create table pr_test(x int, y int); > > aklaver@test=> select * from pr_test where (x, y) between (1, 3) and > (3,2) order by x,y; > x | y > ---+--- > 1 | 3 > 1 | 4 > 2 | 1 > 2 | 2 > 2 | 3 > 2 | 4 > 3 | 1 > 3 | 2 As simple as that? Thank you! I knew there had to be an easy way. Thanks also to the others who replied with a slightly different approach, which involved multiplying x by 10. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
When in doubt, cheat! Why not something like:
SELECT x, y FROM organ_pipes WHERE point(x,y) <@ box(point(?x1,?y1),point(?x2,?y2)) ;
This is definitely a different approach from the others that I've seen.
Basically, think of your channel / piston as a point in a Cartesian plane. And your boundaries as a box in that graph. So you see if the "point" is inside the "box"
On Mon, Oct 5, 2015 at 1:39 PM, Raymond O'Donnell <rod@iol.ie> wrote:
Hello all,
I have an SQL problem which ought to be simple, but I can't get my head
around it.
I have pairs of integers - let's call them (x, y). In effect, x is a
category, while y is an item within that category. For every x, there is
always the same number of integers y; and both x and y are always
numbered sequentially starting from 1.
My problem is that I need to select a list of these pairs, ordered first
on x and then on y, from a given starting point to a given finishing
point and including all pairs in between.
For example, I might have:
x | y
-----
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
2 | 4
(etc)
I then might want to extract a list from, say, (1, 3) to (3, 2), giving:
x | y
-----
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
2 | 4
3 | 1
3 | 2
For the life of me, I can't figure out how to do this. Any help will be
appreciated, or even just a pointer in the right direction. There's
probably something simple that I'm just not seeing....
If anyone's interested, these numbers represent channels and pistons on
the combination system of a largish pipe organ... it's for a hobby project.
Many thanks in advance!
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Schrodinger's backup: The condition of any backup is unknown until a restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
On 10/05/2015 12:00 PM, Raymond O'Donnell wrote: > On 05/10/2015 19:53, Adrian Klaver wrote: >> On 10/05/2015 11:39 AM, Raymond O'Donnell wrote: >>> Hello all, >>> >>> I have an SQL problem which ought to be simple, but I can't get my head >>> around it. >>> >>> I have pairs of integers - let's call them (x, y). In effect, x is a >>> category, while y is an item within that category. For every x, there is >>> always the same number of integers y; and both x and y are always >>> numbered sequentially starting from 1. >>> >>> My problem is that I need to select a list of these pairs, ordered first >>> on x and then on y, from a given starting point to a given finishing >>> point and including all pairs in between. >>> >>> For example, I might have: >>> >>> x | y >>> ----- >>> 1 | 1 >>> 1 | 2 >>> 1 | 3 >>> 1 | 4 >>> 2 | 1 >>> 2 | 2 >>> 2 | 3 >>> 2 | 4 >>> (etc) >>> >>> I then might want to extract a list from, say, (1, 3) to (3, 2), giving: >>> >>> x | y >>> ----- >>> 1 | 3 >>> 1 | 4 >>> 2 | 1 >>> 2 | 2 >>> 2 | 3 >>> 2 | 4 >>> 3 | 1 >>> 3 | 2 >>> >>> For the life of me, I can't figure out how to do this. Any help will be >>> appreciated, or even just a pointer in the right direction. There's >>> probably something simple that I'm just not seeing.... >> >> aklaver@test=> create table pr_test(x int, y int); >> >> aklaver@test=> select * from pr_test where (x, y) between (1, 3) and >> (3,2) order by x,y; >> x | y >> ---+--- >> 1 | 3 >> 1 | 4 >> 2 | 1 >> 2 | 2 >> 2 | 3 >> 2 | 4 >> 3 | 1 >> 3 | 2 > > As simple as that? Thank you! I knew there had to be an easy way. Yea, surprised me to. > > Thanks also to the others who replied with a slightly different > approach, which involved multiplying x by 10. > > Ray. > -- Adrian Klaver adrian.klaver@aklaver.com
On 05/10/2015 20:03, John McKown wrote: > When in doubt, cheat! Why not something like: > > SELECT x, y FROM organ_pipes WHERE point(x,y) <@ > box(point(?x1,?y1),point(?x2,?y2)) ; > > This is definitely a different approach from the others that I've seen. > > > Basically, think of your channel / piston as a point in a Cartesian > plane. And your boundaries as a box in that graph. So you see if the > "point" is inside the "box" Ohhh - that's nice! Reminds of one of my lecturers from college days, who said there was often a geometrical solution to an algebraic problem. Thanks! Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 05/10/2015 20:08, Adrian Klaver wrote: > On 10/05/2015 12:00 PM, Raymond O'Donnell wrote: >> On 05/10/2015 19:53, Adrian Klaver wrote: >>> On 10/05/2015 11:39 AM, Raymond O'Donnell wrote: >>>> Hello all, >>>> >>>> I have an SQL problem which ought to be simple, but I can't get my head >>>> around it. >>>> >>>> I have pairs of integers - let's call them (x, y). In effect, x is a >>>> category, while y is an item within that category. For every x, >>>> there is >>>> always the same number of integers y; and both x and y are always >>>> numbered sequentially starting from 1. >>>> >>>> My problem is that I need to select a list of these pairs, ordered >>>> first >>>> on x and then on y, from a given starting point to a given finishing >>>> point and including all pairs in between. >>>> >>>> For example, I might have: >>>> >>>> x | y >>>> ----- >>>> 1 | 1 >>>> 1 | 2 >>>> 1 | 3 >>>> 1 | 4 >>>> 2 | 1 >>>> 2 | 2 >>>> 2 | 3 >>>> 2 | 4 >>>> (etc) >>>> >>>> I then might want to extract a list from, say, (1, 3) to (3, 2), >>>> giving: >>>> >>>> x | y >>>> ----- >>>> 1 | 3 >>>> 1 | 4 >>>> 2 | 1 >>>> 2 | 2 >>>> 2 | 3 >>>> 2 | 4 >>>> 3 | 1 >>>> 3 | 2 >>>> >>>> For the life of me, I can't figure out how to do this. Any help will be >>>> appreciated, or even just a pointer in the right direction. There's >>>> probably something simple that I'm just not seeing.... >>> >>> aklaver@test=> create table pr_test(x int, y int); >>> >>> aklaver@test=> select * from pr_test where (x, y) between (1, 3) and >>> (3,2) order by x,y; >>> x | y >>> ---+--- >>> 1 | 3 >>> 1 | 4 >>> 2 | 1 >>> 2 | 2 >>> 2 | 3 >>> 2 | 4 >>> 3 | 1 >>> 3 | 2 >> >> As simple as that? Thank you! I knew there had to be an easy way. > > Yea, surprised me to. Just to complete my understanding of what's going on here, how does Postgres see the construct (x, y)? Is it some sort of anonymous or temporary composite type? Thanks, Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 10/05/2015 12:33 PM, Raymond O'Donnell wrote: > On 05/10/2015 20:08, Adrian Klaver wrote: >> On 10/05/2015 12:00 PM, Raymond O'Donnell wrote: >>> On 05/10/2015 19:53, Adrian Klaver wrote: >>>> On 10/05/2015 11:39 AM, Raymond O'Donnell wrote: >>>>> Hello all, >>>>> >>>>> I have an SQL problem which ought to be simple, but I can't get my head >>>>> around it. >>>>> >>>>> I have pairs of integers - let's call them (x, y). In effect, x is a >>>>> category, while y is an item within that category. For every x, >>>>> there is >>>>> always the same number of integers y; and both x and y are always >>>>> numbered sequentially starting from 1. >>>>> >>>>> My problem is that I need to select a list of these pairs, ordered >>>>> first >>>>> on x and then on y, from a given starting point to a given finishing >>>>> point and including all pairs in between. >>>>> >>>>> For example, I might have: >>>>> >>>>> x | y >>>>> ----- >>>>> 1 | 1 >>>>> 1 | 2 >>>>> 1 | 3 >>>>> 1 | 4 >>>>> 2 | 1 >>>>> 2 | 2 >>>>> 2 | 3 >>>>> 2 | 4 >>>>> (etc) >>>>> >>>>> I then might want to extract a list from, say, (1, 3) to (3, 2), >>>>> giving: >>>>> >>>>> x | y >>>>> ----- >>>>> 1 | 3 >>>>> 1 | 4 >>>>> 2 | 1 >>>>> 2 | 2 >>>>> 2 | 3 >>>>> 2 | 4 >>>>> 3 | 1 >>>>> 3 | 2 >>>>> >>>>> For the life of me, I can't figure out how to do this. Any help will be >>>>> appreciated, or even just a pointer in the right direction. There's >>>>> probably something simple that I'm just not seeing.... >>>> >>>> aklaver@test=> create table pr_test(x int, y int); >>>> >>>> aklaver@test=> select * from pr_test where (x, y) between (1, 3) and >>>> (3,2) order by x,y; >>>> x | y >>>> ---+--- >>>> 1 | 3 >>>> 1 | 4 >>>> 2 | 1 >>>> 2 | 2 >>>> 2 | 3 >>>> 2 | 4 >>>> 3 | 1 >>>> 3 | 2 >>> >>> As simple as that? Thank you! I knew there had to be an easy way. >> >> Yea, surprised me to. > > Just to complete my understanding of what's going on here, how does > Postgres see the construct (x, y)? Is it some sort of anonymous or > temporary composite type? This is getting a bit out of my depth, but I figured the behavior is explained here: http://www.postgresql.org/docs/9.4/static/sql-expressions.html 4.2.13. Row Constructors I took the shortcut described here: "The key word ROW is optional when there is more than one expression in the list." The more complete form of the query above is: test=> select * from pr_test where ROW(x, y) between (1, 3) and (3,2) order by x,y;; x | y ---+--- 1 | 3 1 | 4 2 | 1 2 | 2 2 | 3 2 | 4 3 | 1 3 | 2 (8 rows) The comparison procedure is described here: http://www.postgresql.org/docs/9.4/static/functions-comparisons.html 9.23.5. Row Constructor Comparison > > Thanks, > > Ray. > -- Adrian Klaver adrian.klaver@aklaver.com
> aklaver@test=> create table pr_test(x int, y int); > > aklaver@test=> select * from pr_test where (x, y) between (1, 3) and > (3,2) order by x,y; > x | y > ---+--- > 1 | 3 > 1 | 4 > 2 | 1 > 2 | 2 > 2 | 3 > 2 | 4 > 3 | 1 > 3 | 2 +1, nice.
Charles Clavadetscher wrote: >> aklaver@test=> create table pr_test(x int, y int); >> >> aklaver@test=> select * from pr_test where (x, y) between (1, 3) and >> (3,2) order by x,y; >> x | y >> ---+--- >> 1 | 3 >> 1 | 4 >> 2 | 1 >> 2 | 2 >> 2 | 3 >> 2 | 4 >> 3 | 1 >> 3 | 2 > > +1, nice. And the really cool thing about it is that it will work well with a combined index on (x, y). Yours, Laurenz Albe