> >
> > > I still am unclear which of these are valid SQL:
> > >
> > > select a as b from test order by a
> > > select a as b from test order by b
> > >
> > Both are valid, and don't forget the third variant:
> >
> > select a as b from test order by 1
> >
> > Andreas
> >
>
> I wonder why this should be valid. Consider the following
> test case:
>
> CREATE TABLE t1 (a int4, b int4);
> SELECT a AS b, b AS a FROM t1 GROUP BY a, b;
>
> Is that now GROUP BY 1,2 or BY 2,1? Without the grouping, it
>
The order of the columns in a group by don't affect the result.
It will affect the sort order, but without an order by, the order is
implementation depentent and not guaranteed by ANSI.
> is a totally valid statement because the column DISPLAY-names
> given with AS don't affect the rest of it.
>
Resumee:group by and where ignores alias completely (in Oracle and Informix)order by uses alias (only if unambiguous
inInformix, alias precedes column name
in Oracle)
So I guess our group by code does it different, than all others :-(
At last what about this, even if it is how the others do it, it is not
consistent with
our group by:
regression=> select a as b, b as c from a where c=3;
b|c
-+-
3|1
(1 row)
Does anyone know what standard says ?
Andreas
