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
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