Re: [SQL] grouping/clustering query
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. Joe -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Postgres-sql-php
I guess you can change a little the query to your needs. The problem is pretty much the same... I've used c3 column in equality, but if this column has repeated values, just choose any column or combination of columns which is unique. Best, Oliveiros SELECT a.c1,a.c2,b.c3,b.c4,a.c5,b.c6 FROM ( SELECT c1,c2,c5, MIN(c3)as primeiraFROM t1 GROUP BY c1,c2,c5 ) a RIGHT JOIN t1 b ON b.c3 = a.primeira AND b.c1 = a.c1 AND a.c2 = b.c2 AND a.c5 = b.c5 2008/10/23 Zied Kharrat <[EMAIL PROTECTED]> > Really, i have this schema: > > *c1 c2 c3 c4 c5 c6* > *v1* *v2* v3 v4 *v5* v6 > *v1* *v2* v7 v8 *v5* v9 > *v1* *v2* v10v11*v5* v12 > > how can i do my sql request to obtain this? > > *c1 c2 c3 c4 c5 c6* > *v1* *v2* v3 v4 *v5* v6 > ** v7 v8 ** v9 > * * v10v11* * v12 > > > Thank u very much :) > -- even the biggest failure, even the worst mistake, beats the hell out of never trying... - Meredith Grey
Re: [SQL] grouping/clustering query
At 10:20 PM 10/22/2008, you wrote: Message-ID: <[EMAIL PROTECTED]> Date: Wed, 22 Oct 2008 12:14:49 +0700 From: "David Garamond" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Postgres-sql-php
Just add two conditions on the previous query A particularity of this approach is that the non-null record will always appear with the first child in alphabetical order. But, according to what you explain, I guess it is OK, and if it doesn't it is easily changed. :-) Also I've changed the first condition on the right outer join coz it would give trouble if two childs happened to be of the same age. I guess it will never happen two childs with the same name :p Best, Oliveiros SELECT a.num,a.father,b.child,b.age FROM ( SELECT num,father, MIN(child)as primeira FROM t1 GROUP BY num,father) a RIGHT JOIN t1 b ON b.child = a.primeira AND b.father = a.father AND a.num = b.num -- even the biggest failure, even the worst mistake, beats the hell out of never trying... - Meredith Grey 2008/10/23 Zied Kharrat <[EMAIL PROTECTED]> > hi, > > i don't want any sort.. just like this example > > *num father child age col5 > *1 joe bruce14 8 >lei 10 >mike 5 > > 2manuel child135 16 > child233 > child3 30 > > > this is what i want really.. > > What can be then the sql request without sort with this update.. Thanks :) >
Re: [SQL] Postgres-sql-php
hi, i don't want any sort.. just like this example *num father child age col5 *1 joe bruce14 8 lei 10 mike 5 2manuel child135 16 child233 child3 30 this is what i want really.. What can be then the sql request without sort with this update.. Thanks :)
Re: [SQL] Postgres-sql-php
Howdy, Zied.
The query below outputs the results as you want, but
I suspect you have a more general situation you want to solve.
If you have more than one father, say "manuel", you would want something like
this ?
num father child age
1 joe bruce14
lei 10
mike 5
2manuel child135
child233
child3 30
Confirm, please .
Also, do you want the output ordered by age? always?
If so , tell me and we can tweak a little the query to best-fit your needs
Best,
Oliveiros
SELECT a.num,a.father,b.child,b.age
FROM
(
SELECT num,father, MAX(age)as maximo
FROM t1
GROUP BY num,father) a
RIGHT JOIN t1 b
ON b.age = a.maximo
- Original Message -
From: Zied Kharrat
To: pgsql-sql@postgresql.org
Sent: Thursday, October 23, 2008 9:14 AM
Subject: [SQL] Postgres-sql-php
Hi Everybody..
Let's present my problem:
I have a table named t1 and i will insert differents values like this :
insert into t1 (num,father,child,age) values ('1','joe','bruce','14',);
insert into t1 (num,father,child,age) values ('1','joe','lei','10',);
insert into t1 (num,father,child,age) values ('1','joe','mike','5',);
when i use select * from t1 i obtain:
num father child age
1 joe bruce14
1 joe lei 10
1 joe mike 5
i want to have
num father child age
1 joe bruce14
lei 10
mike 5
what can i do as select request to obtain this capture?
Thanks :)
Re: [SQL] SELECT multiple MAX(id)s ?
On Tuesday 14 October 2008 18:27:01 Fernando Hevia wrote: > > -Mensaje original- > > De: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] En nombre de Aarni Ruuhimäki > > Enviado el: Viernes, 10 de Octubre de 2008 07:56 > > Para: pgsql-sql@postgresql.org > > Asunto: [SQL] SELECT multiple MAX(id)s ? > > > > Hello list, > > > > table diary_entry > > > > entry_id SERIAL PK > > d_entry_date_time timestamp without time zone > > d_entry_company_id integer d_entry_location_id integer > > d_entry_shift_id integer d_user_id integer d_entry_header text ... > > > > Get the last entries from companies and their locations? > > > > The last, i.e. the biggest entry_id holds also the latest > > date value within one company and its locations. One can not > > add an entry before the previuos one is 'closed'. Names for > > the companies, their different locations, or outlets if you > > like, users and shifts are stored in company, location, user > > and shift tables respectively. > > > > Again something I could do with a bunch of JOIN queries and > > loops + more LEFT JOIN queries within the output loops, but > > could this be done in a one single clever (sub select?) query? > > > > Output (php) should be something like: > > > > Date | User | Shift | Company | Location > > - > > > > 02.10.2008 | Bobby | Nightshift 1 | Company 1 | Location X > > 04.10.2008 | Brian | Dayshift 2 | Company 1 | Location Y > > 09.10.2008 | Jill | Dayshift 1 | Company 2 | Location A > > 05.10.2008 | Jane | Dayshift 1 | Company 2 | Location B > > 07.10.2008 | Frank | Dayshift 2 | Company 2 | Location C ... > > > > Someone please give me a start kick? > > > > TIA and have a nice weekend too! > > > > -- > > Aarni > > > > Burglars usually come in through your windows. > > Aarni, you should take a look at aggregate functions. > Anyway, I think this is what you are asking for: > > select max(d.d_entry_date_time) as Date, u.name, s.shift, c.name, > l.location_name > from diary_entry d, company c, location l, user u, shift s > where d.d_entry_company_id = c.company_id >and d.d_entry_location_id = l.location_id >and d.d_user_id = u.user_id >and d.d_entry_shift_id = s.shift_id > group by u.name, s.shift, c.name, l.location_name > order by d.d_entry_date_time > > Cheers. Thanks Fernando! I will try this out. Although I already did it in the more clumsy way ... Very best regards, Aarni -- Burglars usually come in through your windows. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Postgres-sql-php
Hi Everybody..
Let's present my problem:
I have a table named *t1* and i will insert differents values like this :
insert into t1 (num,father,child,age) values ('1','joe','bruce','14',);
insert into t1 (num,father,child,age) values ('1','joe','lei','10',);
insert into t1 (num,father,child,age) values ('1','joe','mike','5',);
when i use select * from t1 i obtain:
*num father child age*
1 joe bruce14
1 joe lei 10
1 joe mike 5
i want to have
*num father child age*
1 joe bruce14
lei 10
mike 5
what can i do as select request to obtain this capture?
Thanks :)
