Re: is there a way to deliver an array over column from a query window?

W dniu 04/26/2013 09:54 PM, Misa Simic pisze:
> 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?

Hmm... actualy, it looks like it does. I wouldn't thought, that the sort
order is maintaned from subquery, but if it does, this is just it.

It looks like I've just overdone the solution.

-R

>
>
> 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?= <rafal(at)ztk-rp(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>