Re: group by/order by problem
Date: Tue, 25 May 2010 08:25:27 -0700 (PDT)
Message-ID: <c06538ca-f94e-4803-9a01-6742251e6cc6_at_a16g2000vbr.googlegroups.com>
On May 24, 8:27 am, Serge Rielau <srie..._at_ca.ibm.com> wrote:
> Ken,
>
> I'm curious....
> Does is work if you do ORDER BY 2 (I.e. the second column)? Does it fail
> or give wrong results. The first would be a limitation, the second would
> be a bug...
>
> ORDER BY name resolution is supposed operate to operate on the SELECT
> list since, semantically it happens afterwards:
>
> db2 => create table b_data(player varchar2(10), hp NUMBER(5));
> DB20000I The SQL command completed successfully.
> db2 => insert into b_data values ('Jo', 1), ('Jo', 1), ('Jill', 1),
> ('Jill', 1), ('Jill', 1);
> DB20000I The SQL command completed successfully.
> db2 => select player, sum(hp) thp from b_data where hp = 1 group by
> player order by thp;
>
> PLAYER THP
> ---------- ------------------------------------------
> Jo 2
> Jill 3
>
> 2 record(s) selected.
>
> db2 => select player, sum(hp) thp from b_data where hp = 1 group by
> player order by thp desc;
>
> PLAYER THP
> ---------- ------------------------------------------
> Jill 3
> Jo 2
>
> 2 record(s) selected.
>
> --
> Serge Rielau
> SQL Architect DB2 for LUW
> IBM Toronto Lab
Thanks guys! Much food for thought.
I'm restructuring everything, not for this issue, but because I'm
looking for a
better-structured solution to my project in terms of divide-and-
conquer.
When I have a chance I'll look into your questions and suggestions
further - given
that Vladimir's suggestion works, that's fine for the moment.
I don't want to give up on a theoretical understanding of the issue
( I
kind of figured 'materialized' was as Vladimir described it given the
difference between 'view' and 'materialized view', and the suggestions
about using explain plan to see exactly what Oracle thinks it's doing
I'm going to explore in those llittle bits of time I have available.
Thanks again! Received on Tue May 25 2010 - 10:25:27 CDT