--- Dan <[EMAIL PROTECTED]> wrote: > i.e., if we have: > > CREATE TABLE x1(a, b, c); > CREATE TABLE x2(a, b, c); > > then the following pairs of statements are equivalent: ... > > SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b; > SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2;
Don't you mean ORDER BY 1? SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b; SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1; <-- I thought *only* the leftmost SELECT in the compound chain governs the selection of the column names used by the ORDER BY. The names of the subsequent compound SELECTs should be ignored. At least that's how it works on MySQL and other databases I've used: given: create table x1(a INT, b INT, c INT); insert into x1 values(1, 2, 3); create table x2(a INT, b INT, c INT); insert into x2 values(9, 0, 4); mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b; +------+------+ | b | a | +------+------+ | 2 | 1 | | 9 | 0 | +------+------+ mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 2; +------+------+ | b | a | +------+------+ | 9 | 0 | | 2 | 1 | +------+------+ mysql> SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1; +------+------+ | b | a | +------+------+ | 2 | 1 | | 9 | 0 | +------+------+ Oracle has the same behavior as MySQL, as I recall. sqlite 3.5 produces a different result since it appears to be getting the column name from the rightmost compound select: SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b; x1.b|a 9|0 2|1 Compare MySQL: create table x1(a INT, b INT, c INT); insert into x1 values(1, 2, 3); create table g2(x INT, y INT, z INT); insert into g2 values(9, 0, 4); mysql> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY y; ERROR 1054 (42S22): Unknown column 'y' in 'order clause' to sqlite: sqlite> SELECT x1.b, a FROM x1 UNION SELECT x, y FROM g2 ORDER BY y; 9|0 2|1 > To my mind, the logical change to make would be to allow this: > > SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY "x1.b"; > SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY [x1.b]; > SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY 1; This query is (also) unambiguous given the logic outlined above: SELECT x1.b, a FROM x1 UNION SELECT a, b FROM x2 ORDER BY b; I thought all of this was already hashed in this thread: http://www.mail-archive.com/sqlite-users@sqlite.org/msg23985.html ____________________________________________________________________________________ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
