Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Aggregation function and rows order
> 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