Re: group by default order

"Serge Rielau" <srielau_at_ca.ibm.com> wrote in message news:3hsvecFihm5jU2_at_individual.net...
> hkongogo_at_gmail.com wrote:
>> given a table
>>
>> create table product
>> (
>> product_id integer primary key,
>> category varchar2(100),
>> sale_count integer
>> )
>>
>> what would be the order of
>>
>> select category, sum(sale_count) from product group by category;
>>
>> seems the order in 8i and 9i are different
>>
> There are many ways for a DBMS to do grouping, only some of which will
> result in a complete order.
> Imagine the table being range partitioned on sale_count.
> The DBMS can run local group by-s without ever getting a total order.
> Other options involve hashing..
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab

the retrieval order without an explicit ORDER BY would also be affected by the physical order of the rows, which will very likely differ in the two databases

++ mcs Received on Wed Jun 22 2005 - 06:31:44 CDT