Re: Aggregation function and rows order

From: Patrick Mézard <patrick.mezard_at_ifrance.com>
Date: Thu, 25 Nov 2004 21:06:55 +0100
Message-ID: <41a63b50$0$16327$8fcfb975@news.wanadoo.fr>

Michel Cadot wrote:

> "Patrick Mézard" <patrick.mezard_at_ifrance.com> a écrit dans le message de
> news:41a6221e$0$31291$8fcfb975_at_news.wanadoo.fr...
> 

>>Hello,
>>I have written an aggregation function doing some kind of "join" on
>>string values. Sometimes I would like to aggregate the values in a
>>specific order which I can define via ORDER BY expressions. For
>>instance, I would like to concatenate the strings in lexicographic order.
>>
>>Since the row ordering is not defined in SELECT statement, and by
>>extension in GROUP BY row sets, I guess it cannot be done with SQL only.
>>I was able to write it with PL/SQL cursors but I wonder if there are any
>>extensions to constrain the ordering of a set of rows in (sub)-select
>>statements.
>>
>>Example:
>>
>>stragg(s in varchar2) return varchar2;
>>is the aggregation function.
>>
>>mystringorder_t is an object type constructible from varchar2, with an
>>overloaded ORDER MEMBER function, to define a custom comparison function.
>>
>>create table t
>>(
>> id number,
>> data varchar2(32)
>>);
>>
>>-- Ideally, I'd like to write that.
>>select id, stragg(data) from t
>> group by id order by id, mystringorder_t(data);
>>
>>Patrick Mézard

> 
> 
> May be:
> select id, stragg(data)
> from (select id, data from t order by id, mystringorder_t(data))
> group by id;
> 

I tried this one, but the ordering was not kept in the enclosing select statement, which is compliant with the rules regarding rows ordering I think. It is sad the documentation does not say a word about this in the "User Defined Aggregate Function" chapter. If you have definitive statements addressing this in the doc, please give me some references.

Patrick Mézard Received on Thu Nov 25 2004 - 14:06:55 CST