Sql Server 2005: SELECT a AS b, b AS a FROM #t1 ORDER BY a; b a ----------- ----------- 1 2 9 8
SELECT b AS a, a AS b FROM #t1 ORDER BY a; a b ----------- ----------- 2 1 8 9 SELECT a, b AS a FROM #t1 ORDER BY a; Msg 209, Level 16, State 1, Line 1 Ambiguous column name 'a'. SELECT a AS x, b AS x FROM #t1 ORDER BY x; Msg 209, Level 16, State 1, Line 2 Ambiguous column name 'x'. SELECT a AS b, b AS a FROM #t1 WHERE a=1; b a ----------- ----------- 1 2 SELECT a AS b, b AS a FROM #t1 WHERE a=2; b a ----------- ----------- SELECT a AS x, b AS x FROM #t1 WHERE x=1; Msg 207, Level 16, State 1, Line 1 Invalid column name 'x'. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Tuesday, August 19, 2008 2:00 PM To: General Discussion of SQLite Database Subject: SPAM: [sqlite] Correct SQL name resolution on AS clauses in a SELECT? Consider a table: CREATE TABLE t1(a INTEGER, b INTEGER); INSERT INTO t1 VALUES(1,2); INSERT INTO t1 VALUES(9,8); I am reworking (read: trying to fix) the name resolution algorithm in SQLite and I am wondering what is the correct behavior of the following queries? Can anybody tell me what PostgreSQL or MySQL or SQL Server or Oracle do with these? SELECT a AS b, b AS a FROM t1 ORDER BY a; SELECT b AS a, a AS b FROM t1 ORDER BY a; SELECT a, b AS a FROM t1 ORDER BY a; SELECT a AS x, b AS x ORDER BY x; SELECT a AS b, b AS a WHERE a=1; SELECT a AS b, b AS a WHERE a=2; SELECT a AS x, b AS x WHERE x=1; D. Richard Hipp [EMAIL PROTECTED] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
