On 12/11/07, Robert Wishlaw <[EMAIL PROTECTED]> wrote: > 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 > >
SQL Server Compact Edition Version 3.5, Note well ! Output is unformatted from VB.NET WriteLine in an ExecuteReader. CREATE TABLE t1(a int, b int, c int); INSERT INTO t1 VALUES(1,2,4); INSERT INTO t1 VALUES(2, -1000, 5); SELECT a+b AS c FROM t1 WHERE c=4; SQL Server Compact Edition Version 3.5 returns 3 select a AS foo from t1 where foo=1; SQL Server Compact Edition Version 3.5 returns Unhandled Exception: The column name is not valid. [ Node name (if any) = ,Column name = foo ] select a AS foo from t1 where "foo"=1; SQL Server Compact Edition Version 3.5 returns Unhandled Exception: The column name is not valid. [ Node name (if any) = ,Column name = foo ] select a AS "foo" from t1 where "foo"=1; SQL Server Compact Edition Version 3.5 returns Unhandled Exception: The column name is not valid. [ Node name (if any) = ,Column name = foo ] select a AS "foo" from t1 union select b from t1 order by foo; SQL Server Compact Edition Version 3.5 returns -1000 1 2 select a+b AS "c" from t1 union select b from t1 order by c; SQL Server Compact Edition Version 3.5 returns -1000 -998 2 3 select * from t1; SQL Server Compact Edition Version 3.5 returns 1 2 4 2 -1000 5 select a, a+b AS "c" from t1 order by c; SQL Server Compact Edition Version 3.5 returns 2 -998 1 3 select a, a+b AS c from t1 order by c; SQL Server Compact Edition Version 3.5 returns 2 -998 1 3 select a AS foo from t1 union select b from t1 order by foo; SQL Server Compact Edition Version 3.5 returns -1000 1 2 select a, a+b AS c from t1 UNION ALL select a AS c, c AS a from t1 order by c; SQL Server Compact Edition Version 3.5 returns 2 -998 1 3 1 4 2 5 select a, a+b AS c from t1 group by c, a order by c, a; SQL Server Compact Edition Version 3.5 returns Unhandled Exception: In aggregate and grouping expressions, the SELECT clause can contain only aggregates and grouping expressions. [ Select clause = ,b ] Robert Wishlaw ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
