Re: A unique pairs version of UNNEST() ?

Andy Colson <andy@squeakycode.net> wrote:

>> So in an example where list_of_ids is {1,2,3,4,5} I would essentially get:
>>
>> {1, 2}
>> {1, 3}
>> {1, 4}
>> {1, 5}
>> {2, 3}
>> {2, 4}
>> {2, 5}
>> {3, 4}
>> {3, 5}
>> {4, 5}
>>
>>
>> Any tips? Thanks!
>>
>> --
>> Wells Oliver
>> wells.oliver@gmail.com <mailto:wellsoliver@gmail.com>
>
> if you could convert the array to a table then cross join it.  Something
> like:
>
> select a.*, b.*
> from unnest( {1,2,3,4,5} ) a
> cross join unnest( {1,2,3,4,5} ) b
>
> -Andy

not exactly the expectet result (and syntactically wrong), better
solution:


test=*# select (a.*, b.*) from unnest( array[1,2,3,4,5] ) a cross join unnest( array[1,2,3,4,5] ) b where a < b;
  row
-------
 (1,2)
 (1,3)
 (1,4)
 (1,5)
 (2,3)
 (2,4)
 (2,5)
 (3,4)
 (3,5)
 (4,5)
(10 rows)

or

test=*# select array[a.*, b.*] from unnest( array[1,2,3,4,5] ) a cross join unnest( array[1,2,3,4,5] ) b where a < b;
 array
-------
 {1,2}
 {1,3}
 {1,4}
 {1,5}
 {2,3}
 {2,4}
 {2,5}
 {3,4}
 {3,5}
 {4,5}
(10 rows)


(matches the excpected result)


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°