Re: group by/order by problem
From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Sun, 23 May 2010 08:54:42 -0700 (PDT)
Message-ID: <3b34afe7-5691-45d9-bfdf-47548c682567_at_q8g2000vbm.googlegroups.com>
On May 23, 6:45 pm, Ken Quirici <kquir..._at_yahoo.com> wrote:
> Hi,
>
> The following query:
>
> select
> player,
> sum(hp) thp
> from b_data
> where hp = 1
> group by player
> order by thp
> ;
>
> does not sort the results either by player or thp.
>
> Is there some logical reason for this, or is order by only
> designed to sort group by's by the group by column?
>
> Basically, how can I get it to sort by thp?
>
> Any help MUCH appreciated.
>
> Regards,
>
> Ken Quirici
order by thp;
Date: Sun, 23 May 2010 08:54:42 -0700 (PDT)
Message-ID: <3b34afe7-5691-45d9-bfdf-47548c682567_at_q8g2000vbm.googlegroups.com>
On May 23, 6:45 pm, Ken Quirici <kquir..._at_yahoo.com> wrote:
> Hi,
>
> The following query:
>
> select
> player,
> sum(hp) thp
> from b_data
> where hp = 1
> group by player
> order by thp
> ;
>
> does not sort the results either by player or thp.
>
> Is there some logical reason for this, or is order by only
> designed to sort group by's by the group by column?
>
> Basically, how can I get it to sort by thp?
>
> Any help MUCH appreciated.
>
> Regards,
>
> Ken Quirici
select player, thp
from (select player, sum(hp) thp
from b_data where hp = 1 group by player)
order by thp;
To order by thp Oracle needs to "materialize" the inner view, so you need to give it a chance to do so.
Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Sun May 23 2010 - 10:54:42 CDT