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 ) t Doesnt give u desired result? On Friday, April 26, 2013, Rafał Pietrak wrote: > W dniu 04/26/2013 05:25 PM, Tom Lane pisze: > >> =?ISO-8859-2?Q?Rafa=B3_**Pietrak?= <ra...@ztk-rp.eu> writes: >> >>> array_agg(distinct v order by v) -- works in postgres, but actually I >>> need: >>> array_agg(distinct v order by v,x) -- which doesn't. (ERROR: >>> ....expressions must appear in argument list), >>> >> Why do you think you need that? AFAICS, the extra order-by column could >> not in any way affect the result of the operation. >> > > In my particular case (e.g. not in general, since I assume, we all agree, > that people do sort things comming out of the query for one purpose or > another), is that: > 1. the information i retrieve (the V), is a telephone number. > 2. my database does keep numerous contact information (e.g. telephone > numbers, email, etc) for "entities" registered here - e.g people/companies > leave contact information of various relevance: my-private, my-office, > my-lawyer, etc. > 3. when I need to get in touch with somebody, I need to choose the number > that is "most relevant" - one person leaves "my-private" phone, and > "my-lawyer" phone; the other leaves "my-office", and "my-lawyer". > 4. in the above example I'd like to peek: "my-private" for the first > person, and "my-office" for the other. I wouldn't like to relay on > randomness provided by the database query plan. > 5. so I have "the other" column (the X, e.g "my-something"), that I'd like > to sort the array elements by. And peek just the first element of the array. > > BTW: I've just rid off the array, and cooked a plain table join with > "distinct on ()", which gives just what I needed. My initial plan of using > array was to reduce the intermediate row-sets as much as possible as early > as possible. Yet, in this case, plain old RDB joins proved to be better > (may be not faster - a big multitable join is formed along the query, but > conceptually cleaner, which works for me, the database isn't terribly big). > > So I have my problem solved, although I haven't figured out a way to have > controll over the sort order of array_agg() result - which might be > otherwise usefull. > > thnx, > > -R > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general> >
