From: | Steve Midgley <science(at)misuse(dot)org> |
---|---|
To: | Joe <dev(at)freedomcircle(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org,David Garamond <davidgaramond(at)gmail(dot)com> |
Subject: | Re: grouping/clustering query |
Date: | 2008-10-24 15:04:37 |
Message-ID: | 20081024150447.92B2F64FD19@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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');
From | Date | Subject | |
---|---|---|---|
Next Message | Oliveiros Cristina | 2008-10-24 15:20:02 | Re: grouping/clustering query |
Previous Message | Joe | 2008-10-23 18:28:33 | Re: grouping/clustering query |