Re: Selecting pairs of numbers

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