Re: Selecting pairs of numbers

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(at)test=> create table pr_test(x int, y int);
>>>
>>> aklaver(at)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(at)iol(dot)ie