Re: grouping/clustering query

At 11:28 AM 10/23/2008, Joe wrote:
>Steve Midgley wrote:
>>># (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?
>
> From a purely accounting standpoint, since transaction 1 was applied
> to both invoices A and B, you need to group the invoices so that you
> can compare total invoiced against total paid.

I tinkered around briefly but didn't come up with a good idea, but I
bet someone on this list can. However, I did create a CREATE script for
your table design which, in my experience, makes it more likely that a
real expert will take on your problem..

Hope this helps,

Steve

DROP TABLE IF EXISTS trans;

CREATE TABLE trans
(
id serial NOT NULL,
inv_id character varying,
tx_id character varying,
CONSTRAINT pk_id PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

insert into trans (inv_id, tx_id) values('A','1');
insert into trans (inv_id, tx_id) values('A','3');
insert into trans (inv_id, tx_id) values('B','1');
insert into trans (inv_id, tx_id) values('B','2');
insert into trans (inv_id, tx_id) values('C','5');
insert into trans (inv_id, tx_id) values('D','6');
insert into trans (inv_id, tx_id) values('D','7');
insert into trans (inv_id, tx_id) values('E','8');
insert into trans (inv_id, tx_id) values('F','8');