Your script is handy, Steve.
Spontaneously, This seems to be an array type problem, something I just have
vague notions about.
I'll take a look at this,
http://www.postgresql.org/docs/8.3/static/arrays.html to see if something
occurs...
Best,
Oliveiros
----- Original Message -----
From: "Steve Midgley" <[EMAIL PROTECTED]>
To: "Joe" <[EMAIL PROTECTED]>
Cc: <pgsql-sql@postgresql.org>; "David Garamond" <[EMAIL PROTECTED]>
Sent: Friday, October 24, 2008 4:04 PM
Subject: Re: [SQL] 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');
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
