Selecting pairs of numbers

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

____________________________________

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general