Re: Aggregation function and rows order

"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;

-- 
Regards
Michel Cadot