Funky GROUP BY

[Ruslan Zasukhin]

On 10/11/06 9:38 PM, "Ivan Smahin" <ivan_smahin at valentina-db.com> wrote:

> 
> Let me explain using simple example.
> 
> CREATE TABLE t1 ( f1 LONG )
> CREATE TABLE t2 ( f1 LONG )
> 
> -- Workable example.
> --
> 
> SELECT
>       t1.f1  as 'a',
>       t2.f1  as 'b'
> FROM
>     t1, t2
> WHERE
>      t1.f1 = t2.f1
> GROUP BY
>       t1.f1,
>       t2.f1
> 
> --
> 
> 1. "... GROUP BY a, b" does not work because aggregation operates with
> "original" fields, but tables have no fields with such names.
> 
> 2.  "SELECT  t1.f1,  t2.f1  ...  GROUP  BY t1.f1, t2.f1" does not work
> because  aggregation  creates  new  table with fields listed in select
> clause. And both fields have to be named "f1" here - so you get an exception.

Aha,

So we need use alias in SELECT to resolve conflict of names,
But in GROUP BY still must be used original fields.

Clear and simple :-)


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]