Skip site navigation (1) Skip section navigation (2)

Re: grouping/clustering query

From: Steve Midgley <science(at)misuse(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Cc: "David Garamond" <davidgaramond(at)gmail(dot)com>
Subject: Re: grouping/clustering query
Date: 2008-10-23 16:08:48
Message-ID: 20081023160857.220D164FD12@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-sql
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


Responses

pgsql-sql by date

Next:From: Oliveiros CristinaDate: 2008-10-23 18:00:54
Subject: Re: Postgres-sql-php
Previous:From: Oliveiros CristinaDate: 2008-10-23 13:25:45
Subject: Re: Postgres-sql-php

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group