Re: group by/order by problem

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Mon, 24 May 2010 08:27:12 -0400
Message-ID: <85v9krFfblU1_at_mid.individual.net>



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
Received on Mon May 24 2010 - 07:27:12 CDT

Original text of this message