Re: Ordering of data on calls to user defined aggregate.

On Saturday, May 18, 2002, at 03:13 PM, Joel Burton wrote:

> Tim Hart <timjhart(at)shaw(dot)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.