Aggregation function and rows order

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 Received on Thu Nov 25 2004 - 12:19:24 CST