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