SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ) FROM testy k where k.e <> 'email' and k.c='1035049' ORDER BY a, b, c, e
If doesnt work - Probably there is a better option... In worst case I would do SELECT DISTINCT a, b, c, array_agg(d) OVER (PARTITION BY c ) FROM ( SELECT a, b, c, d FROM testy where e <> 'email' and c='1035049' ORDER BY a, b, c, e ) Kind Regards, Misa 2013/4/24 Rafał Pietrak <ra...@zorro.isa-geek.com> > W dniu 03/24/2013 12:11 PM, Rafał Pietrak pisze: > > W dniu 03/24/2013 12:06 PM, Misa Simic pisze: > > maybe, > > SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition by > invoice_nr) from invoices; > > > RIGHT. Thenx. (and the first thing I did, I've read the doc on > array_agg().... what stress makes from people :( > > > Actually, I have a problem with that (which I haven't noticed earlier > because the data I'm having, don't have to many "duplicates" that cause > it). The problem is, that: > -------------------------------------- > SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ) FROM > testy k where k.e <> 'email' and k.c='1035049' ; > a | b | c | array_agg > ------+----------+---------+------------------------------- > 1035 | 10410053 | 1035049 | {9902031328529,5951948640868} > --------------------------------------- > > is _almost_ fine. But I actually need to have control over the order in > which the array gathered its values. So I try: > ------------------------------------ > SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ORDER BY > k.e) FROM testy k where k.e <> 'email' and k.c='1035049' ; > a | b | c | array_agg > ------+----------+---------+------------------------------- > 1035 | 10410053 | 1035049 | {5951948640868} > 1035 | 10410053 | 1035049 | {5951948640868,9902031328529} > (2 rows) > -------------------------------------- > > And this is not at all what I've expected - the aggerate function returned > different values over the selected partition. > > I understand, that this behavior (of changing the aggregate function > return values) is there for the purpose of having sum() - and the like - > aggregate functions return accumulating/averaged/etc values as of the > example in postgres documentation ( > http://www.postgresql.org/docs/9.1/static/tutorial-window.html) > > But the array_agg() is significantly different from other aggregate > functions - it maintains all the trasspassed values within; under such > circumstances: is it reasonable to copy that functionality (of PARTITION > OVER ... ORDER BY...) in it? > A particular value relevant to a particular row (when SELECT withiout > DISTINCT) can be retrieved by RANK() function used as an index into the > resulting array. > > But, if (unfortunately) this functionality have to stay: Can somebody pls > help me cooking an SQL that returns the same value of array_agg() over the > entire partition, while letting me control the order of aggregated values, > based on the order of column "E"? > > My table for the showcase was: > ----------------------------------- > SELECT * FROM testy; > a | b | c | d | e > ------+----------+---------+----------------------+------- > 1035 | 10410053 | 1035049 | 9902031328529 | tel > 1035 | 10410053 | 1035049 | 5291286...@gmail.com | email > 1035 | 10410053 | 1035049 | 5951948640868 | tel2 > (3 rows) > ------------------------------------------ > > thx > > -R >