Re: Aggregation function and rows order

From: Adrian Billington <billiauk_at_yahoo.co.uk>
Date: 26 Nov 2004 02:26:52 -0800
Message-ID: <dee17a9f.0411260226.356a95a8@posting.google.com>

Patrick,

As you are grouping by ID, surely you will only have one aggregated string, so there will be nothing to order by ?

If you actually want to order the aggregated string, then I've shown you how to do it below. As you didn't add your ordering rules, I've gone for alphabetical order. I've used a function instead of a type that breaks down the aggregated string and re-orders it. A function is used because types cannot have associative array attributes.

10g>
10g> create table t
  2 (

  3       id number,
  4       data varchar2(32)

  5 );

Table created.

10g>
10g> insert all

  2  into t values ( 1, 'JJJ' )
  3  into t values ( 1, 'XXX' )
  4  into t values ( 1, 'CCC' )
  5  into t values ( 2, 'YYY' )
  6  into t values ( 2, 'DDD' )
  7  into t values ( 2, 'BBB' )
  8  into t values ( 2, 'YYY' )

  9 select 'x'
 10 from dual;

7 rows created.

10g>
10g> select id
  2 , stragg(data) as data_string
  3 from t
  4 group by
  5 id
  6 order by
  7 id;

        ID DATA_STRING
---------- --------------------

         1 JJJ,XXX,CCC
         2 YYY,YYY,DDD,BBB

2 rows selected.

10g> 
10g> 
10g> create function order_string (
  2                  string_in in varchar2,
  3                  delimiter_in in varchar2 default ','
  4                  ) return varchar2 as
  5  
  6     type aat_string is table of number
  7        index by varchar2(4000);
  8     aa_strings aat_string;
  9  
 10     v_wkg_str   varchar2(4001) := string_in || delimiter_in;
 11     v_pos       pls_integer;
 12     v_indx      varchar2(4000);
 13     v_retstring varchar2(4000);

 14
 15 begin

 16     -- Decompose the string...
 17     loop
 18        v_pos := instr(v_wkg_str,delimiter_in);
 19        exit when nvl(v_pos,0) = 0;
 20        v_indx := trim(substr(v_wkg_str,1,v_pos-1));
 21        aa_strings( v_indx ) := case
 22                                   when aa_strings.exists( v_indx )
 23                                   then aa_strings( v_indx ) + 1
 24                                   else 1
 25                                end;
 26        v_wkg_str := substr(v_wkg_str,v_pos+1);
 27     end loop;
 28     -- Now put it back together...
 29     v_indx := aa_strings.first;
 30     while v_indx is not null loop
 31        for i in 1 .. aa_strings( v_indx ) loop
 32           v_retstring := v_retstring || delimiter_in || v_indx;
 33        end loop;
 34        v_indx := aa_strings.next( v_indx );
 35     end loop;
 36  
 37     return ltrim( v_retstring, delimiter_in );

 38
 39 end order_string;
 40 /

Function created.

10g>
10g> select id

  2  ,      stragg(data) as data_string
  3  ,      order_string(stragg(data)) as ordered_data_string

  4 from t
  5 group by
  6 id
  7 order by
  8 id;

        ID DATA_STRING ORDERED_DATA_STRING
---------- -------------------- --------------------

         1 JJJ,XXX,CCC          CCC,JJJ,XXX
         2 YYY,YYY,DDD,BBB      BBB,DDD,YYY,YYY

2 rows selected.

10g> 
10g> --
10g> -- So if you want to order by it, you can but this is an

expensive
10g> -- approach, especially if Oracle merges the query. But anyway, this

10g> -- shows you the ordering working...
10g> --
10g> select id
  2  ,      data_string
  3  from  (
  4         select id
  5         ,      stragg(data) as data_string
  6         from   t
  7         group  by
  8                id
  9        )
 10  order  by
 11         order_string(data_string);

        ID DATA_STRING


---------- --------------------

         2 YYY,YYY,DDD,BBB
         1 JJJ,XXX,CCC

2 rows selected.

Regards
Adrian Received on Fri Nov 26 2004 - 04:26:52 CST