On 12/11/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- [EMAIL PROTECTED] wrote:
> > The current name resolution rules for SQLite are that it
> > first tries to resolve names using just the tables in the
> > join. If that fails, then it looks at result column aliases.
> > I think that approach continues to work on WHERE. But I need
> > to reverse the search order on ORDER BY - the column aliases
> > need to take precedence over tables in the join.
>
> Should GROUP BY follow the WHERE resolution rules or the proposed
> new ORDER BY rules?
>
> Given:
>
> create table t1(a INT, b INT, c INT);
> insert into t1 values(1, 2, 4);
> insert into t1 values(2, -1000, 5);
>
> mysql> select a, a+b AS c from t1 group by c, a order by c, a;
> +------+------+
> | a | c |
> +------+------+
> | 2 | -998 |
> | 1 | 3 |
> +------+------+
>
> postgres=> select a, a+b AS c from t1 group by c, a order by c, a;
> ERROR: column "t1.b" must appear in the GROUP BY clause or be used in an
> aggregate function
>
IBM DB2 9.5
------------------------------ Commands Entered ------------------------------
select a, a+b AS c from t1 order by c;
select a AS foo from t1 union select b from t1 order by foo;
select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 order by c;
select a, a+b AS c from t1 group by c, a order by c, a;
------------------------------------------------------------------------------
select a, a+b AS c from t1 order by c
A C
----------- -----------
2 -998
1 3
2 record(s) selected.
select a AS foo from t1 union select b from t1 order by foo
SQL0206N "FOO" is not valid in the context where it is used. SQLSTATE=42703
select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 order by c
SQL0206N "C" is not valid in the context where it is used. SQLSTATE=42703
select a, a+b AS c from t1 group by c, a order by c, a
SQL0119N An expression starting with "B" specified in a SELECT clause, HAVING
clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in
a SELECT clause, HAVING clause, or ORDER BY clause with a column function and
no GROUP BY clause is specified. SQLSTATE=42803
Robert Wishlaw
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
