group by/order by problem
in [Oracle]
|
From: Ken Quirici on 23 May 2010 10:45 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
From: Vladimir M. Zakharychev on 23 May 2010 11:54 On May 23, 6:45 pm, Ken Quirici <kquir...(a)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
From: Ken Quirici on 23 May 2010 12:29 On May 23, 11:54 am, "Vladimir M. Zakharychev" <vladimir.zakharyc...(a)gmail.com> wrote: > On May 23, 6:45 pm, Ken Quirici <kquir...(a)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 Hi Vladimir, Thanks for your reply. I've changed the database structure in the meantime and haven't changed the code to repopulate it, so I can't check your revised version yet. However you don't need to 'materialize' the inner view in something like group by player order by player which works fine. Any thoughts? Regards, Ken Quirici
From: Ken Quirici on 23 May 2010 13:17 On May 23, 11:54 am, "Vladimir M. Zakharychev" <vladimir.zakharyc...(a)gmail.com> wrote: > On May 23, 6:45 pm, Ken Quirici <kquir...(a)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 Hi Vladimir, I got the database restructured, and the revised code debugged. I tried out a query w/ a nested query like you suggested and it worked. Many thanks! However I'm not sure what you mean by 'materialize' the inner view. If there were no group by, there wouldn't be an issue - the order by would 'materialize' whatever query it was a clause of, whatever 'materialize' means. Why does the group by mean the order by can't to the same thing, if instead of a nested query, I simply append the order by clause to the group by query? I'm hope you understand how I don't understand, if you know what I mean. Again many thanks. It's enabled my project to lurch forward! Regards, Ken Quirici
From: Serge Rielau on 24 May 2010 08:27 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
|
Next
|
Last
Pages: 1 2 Prev: How to make DBA_AUDIT_TRAIL�s status to VALID Next: Rman backup of BCV using ASM |