Begin forwarded message:
> From: Tim Hart <timjhart@shaw.ca>
> Date: Sat May 18, 2002 01:52:54 PM US/Mountain
> To: Joel Burton <joel@joelburton.com>
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Ordering of data on calls to user defined
> aggregate.
>
>
> On Saturday, May 18, 2002, at 03:13 PM, Joel Burton wrote:
>
>> Tim Hart <timjhart@shaw.ca> said:
>>
>>> So I tried a query like this:
>>>
>>> select fk, concat_with_and(name) from ( select fk, name from foo order
>>> by fk, name) sub_select group by fk;
>>>
>>> From just eyeballing the first 10 to 12 pages of the results, all
>>> but 2
>>> records had the names in alphabetical order. So I removed the
>>> subselect
>>> and ran the query again - this time paying attention to the ordering
>>> within names. Very few entries in the 'names' column were in
>>> alphabetical order at all.
>>
>> Hmmm... in my (small) test case, they were all alphabetized.
>>
>> I didn't think that subquery sort orders were guaranteed, though, so
>> perhaps it's okay that yours weren't.
>>
>> Can you try with GROUP BY fk, name in the subquery? That works, too,
>> on my small test case, and that should be guaranteed behavior in a
>> subquery. Let's see how that works with your data set.
>>
>> - J.
>>
>>
> I'm not quite sure how you're expecting me to modify the subquery.
> Probably my tired brain cells... ;)
>
> But yes - I've been unable to reproduce the issue with a smaller
> dataset. I didn't want to spend all that extra time creating a
> reproduce-able case if someone could definitively say - as you did
> above - that subquery sort orders were not guaranteed. Given that the
> outer 'group by' has no way of knowing that the result set it's being
> handed is effectively grouped, I'm guessing that the group-by algorithm
> in this case is probably the source of my ills. No biggie.
>
Never mind - I figured it out. I didn't know that you could do 'group
by' without specifying an aggregator in the select. I'll have to file
that away somewhere.
Regardless - I got the same result. In fact, the same few records that
were not in alphabetical order are still not in alphabetical order.