Thread: ordered by join? ranked aggregate? how to?
What I need is to join 2 tables CREATE TABLE master( id INT4 ); CREATE TABLE slave ( master_id INT4, rank INT4, value TEXT); What I need is to make the query: SELECT m.id, array_agg(s.value) AS my_problematic_array FROM master AS m LEFT JOIN slave AS s ON (m.id = s.master_id) GROUP BY m.id; return the 'my_problematic_array' in order specified by slave.rank As you probably can guest I don't have any idea know how to do it :/
In response to wstrzalka : > What I need is to join 2 tables > > CREATE TABLE master( > id INT4 > ); > > > CREATE TABLE slave ( > master_id INT4, > rank INT4, > value TEXT); > > > What I need is to make the query: > > SELECT m.id, array_agg(s.value) AS my_problematic_array > FROM master AS m LEFT JOIN slave AS s ON (m.id = s.master_id) > GROUP BY m.id; > > return the 'my_problematic_array' in order specified by slave.rank > > As you probably can guest I don't have any idea know how to do it :/ test=*# select * from master;id ---- 1 2 (2 rows) test=*# select * from slave;master_id | rank | value -----------+------+------- 1 | 5 | 5 1 | 3 | 3 1 | 7 | 7 (3 rows) test=*# select id, array_agg(unnest) from ( select id, unnest(my_problematic_array) from ( SELECT m.id, array_agg(s.value) AS my_problematic_arrayFROM master AS m LEFT JOIN slave AS s ON (m.id = s.master_id) GROUP BY m.id ) foo order by 1,2 ) bar group by 1;id | array_agg ----+----------- 1 | {3,5,7} 2 | {NULL} (2 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
In response to wstrzalka : > What I need is to join 2 tables > > CREATE TABLE master( > id INT4 > ); > > > CREATE TABLE slave ( > master_id INT4, > rank INT4, > value TEXT); > > > What I need is to make the query: > > SELECT m.id, array_agg(s.value) AS my_problematic_array > FROM master AS m LEFT JOIN slave AS s ON (m.id = s.master_id) > GROUP BY m.id; Faster solution (compared to my other email): test=# select id, array_agg(value) from (SELECT m.id, s.value FROM master AS m LEFT JOIN slave AS s ON (m.id = s.master_id) order by 1,2) foo group by 1;id | array_agg ----+----------- 1 | {3,5,7} 2 | {NULL} (2 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
On 15 Wrz, 09:56, andreas.kretsch...@schollglas.com ("A. Kretschmer") wrote: > In response to wstrzalka : > > > > > What I need is to join 2 tables > > > CREATE TABLE master( > > id INT4 > > ); > > > CREATE TABLE slave ( > > master_id INT4, > > rank INT4, > > value TEXT); > > > What I need is to make the query: > > > SELECT m.id, array_agg(s.value) AS my_problematic_array > > FROM master AS m LEFT JOIN slave AS s ON (m.id = s.master_id) > > GROUP BY m.id; > > Faster solution (compared to my other email): > > test=# select id, array_agg(value) from (SELECT m.id, s.value FROM > master AS m LEFT JOIN slave AS s ON (m.id = s.master_id) order by 1,2) > foo group by 1; > id | array_agg > ----+----------- > 1 | {3,5,7} > 2 | {NULL} > (2 rows) > > Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > > -- > Sent via pgsql-sql mailing list (pgsql-...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-sql Yes. And No :) The problem is that in original query the aggregate can be used more then once :/ So in fact it's like: SELECT m.id, array_agg(s1.value), array_agg(s2.value) FROM master AS m LEFT JOIN slave AS s1 ON (m.id = s1.master_id AND SOME_CONDITION_ON_S1) LEFT JOIN slave AS s2 ON (m.id = s2.master_id AND SOME_OTHER_CONDITION_ON_S1) GROUP BY m.id;
On 15 Wrz, 10:10, wstrzalka <wstrza...@gmail.com> wrote: > On 15 Wrz, 09:56, andreas.kretsch...@schollglas.com ("A. Kretschmer") > wrote: > > > > > In response to wstrzalka : > > > > What I need is to join 2 tables > > > > CREATE TABLE master( > > > id INT4 > > > ); > > > > CREATE TABLE slave ( > > > master_id INT4, > > > rank INT4, > > > value TEXT); > > > > What I need is to make the query: > > > > SELECT m.id, array_agg(s.value) AS my_problematic_array > > > FROM master AS m LEFT JOIN slave AS s ON (m.id = s.master_id) > > > GROUP BY m.id; > > > Faster solution (compared to my other email): > > > test=# select id, array_agg(value) from (SELECT m.id, s.value FROM > > master AS m LEFT JOIN slave AS s ON (m.id = s.master_id) order by 1,2) > > foo group by 1; > > id | array_agg > > ----+----------- > > 1 | {3,5,7} > > 2 | {NULL} > > (2 rows) > > > Andreas > > -- > > Andreas Kretschmer > > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > > > -- > > Sent via pgsql-sql mailing list (pgsql-...@postgresql.org) > > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-sql > > Yes. And No :) > > The problem is that in original query the aggregate can be used more > then once :/ > > So in fact it's like: > > SELECT m.id, array_agg(s1.value), array_agg(s2.value) > FROM master AS m LEFT JOIN slave AS s1 ON (m.id = s1.master_id AND > SOME_CONDITION_ON_S1) > LEFT JOIN slave AS s2 ON (m.id = > s2.master_id AND SOME_OTHER_CONDITION_ON_S1) > GROUP BY m.id; However it still may work as the order by is the same :) Will try ...