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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
