Re: grouping/clustering query

At 10:20 PM 10/22/2008, you wrote:
>Message-ID:
><7c33d060810212214h5c85b406i49dd9e8d76bec9bd(at)mail(dot)gmail(dot)com>
>Date: Wed, 22 Oct 2008 12:14:49 +0700
>From: "David Garamond" <davidgaramond(at)gmail(dot)com>
>To: pgsql-sql(at)postgresql(dot)org
>Subject: grouping/clustering query
>X-Archive-Number: 200810/89
>X-Sequence-Number: 31731
>
>Dear all,
>
>I have an invoices (inv) table and bank transaction (tx) table.
>There's also the payment table which is a many-to-many relation
>between the former two tables, because each invoice can be paid by one
>or more bank transactions, and each bank transaction can pay for one
>or more invoices. Example:
>
># (invoiceid, txid)
>(A, 1)
>(A, 3)
>(B, 1)
>(B, 2)
>(C, 5)
>(D, 6)
>(D, 7)
>(E, 8)
>(F, 8)
>
>For journalling, I need to group/cluster this together. Is there a SQL
>query that can generate this output:
>
># (journal: invoiceids, txids)
>[A,B] , [1,2,3]
>[C], [5]
>[D], [6,7]
>[E,F], [8]

Hi Dave,

I'm not following the logic here. A has 1,3 and B has 1,2. So why does
the first line print:

>[A,B] , [1,2,3]

What's the rule that tells the query to output this way? Is it that all
of B's values are between A's values?

Also in your output, you've indicated [A,B] - does this mean you want
two columns of output, each column being a pg array?

I may not be the best person to answer the actual SQL question, but I
thought I'd clarify your requirements so the list members can have the
best chance of answering.

Steve