Re: group by/order by problem

From: Ken Quirici <kquirici_at_yahoo.com>
Date: Tue, 25 May 2010 08:27:13 -0700 (PDT)
Message-ID: <a80f4815-415a-45ce-97da-13321489b941_at_k31g2000vbu.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

One last thing - I'm work on personal, proprietary software/database with commercial
potential so I have to figure out how to present info on it. Thanks!

Regards,

Ken Quirici Received on Tue May 25 2010 - 10:27:13 CDT

Original text of this message