Quoth Waldemar Derr <m...@wladid.de>, on 2010-11-26 12:24:27 +0100: > --Don't working: (Error: 1st ORDER BY term does not match any column in the > result set.) > > SELECT * FROM OrderTest WHERE Price < 200 > UNION > SELECT * FROM OrderTest WHERE Price > 500 > ORDER BY Price IS 0, Price;
>From http://sqlite.org/lang_select.html: | Otherwise, if the ORDER BY expression is any other expression, it is | evaluated and the the returned value used to order the output rows. If | the SELECT statement is a simple SELECT, then an ORDER BY may contain | any arbitrary expressions. However, if the SELECT is a compound | SELECT, then ORDER BY expressions that are not aliases to output | columns must be exactly the same as an expression used as an output | column. > Is this a bug? In MySQL it works as expected. Is there a workaround? Add another output column with the expression you want, give it a name, and ORDER BY that name. My guess is that this is because the multiple output column specification parts of a compound SELECT may result in different intrepretations of an arbitrary expr in that position, and it's not clear how any kind of inwards propagation would work without yielding surprising results. ---> Drake Wilson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
