Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Aggregation function and rows order

Re: Aggregation function and rows order

From: Adrian Billington <billiauk_at_yahoo.co.uk>
Date: 30 Nov 2004 00:51:59 -0800
Message-ID: <dee17a9f.0411300051.7a4a92e9@posting.google.com>


> Ok, instead of relying on rows order to perform the aggregation, you
> aggregate out-of-order then re-order the string.

The problem is that aggregates by their very nature do not enable ordering. See the following where we pass in the values in order, but even then they come out unordered.

10g> select id
  2 , data
  3 from (

  4         select id
  5         ,      data
  6         from   t
  7         order  by
  8                id
  9         ,      data
 10        );

        ID DATA
---------- --------------------------------
         1 CCC
         1 JJJ
         1 XXX
         2 BBB
         2 DDD
         2 YYY
         2 YYY

7 rows selected.

10g>
10g> select id
  2 , stragg(data) as data_string
  3 from (

  4         select id
  5         ,      data
  6         from   t
  7         order  by
  8                id
  9         ,      data
 10        )
 11  group  by
 12         id;

        ID DATA_STRING
---------- ------------------------------
         1 CCC,JJJ,XXX
         2 BBB,YYY,DDD,YYY

2 rows selected.

We just can't get that aggregate function to order our values and just in case it was a function of parallel merging, I disabled parallel for the stragg function.

Good luck !

Regards
Adrian Received on Tue Nov 30 2004 - 02:51:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US