On 26-11-10 12:37, Drake Wilson wrote:
> 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
The extra column seems to work,
but i thought this should work too?
But the ORDER is wrong...
sqlite> SELECT a,b FROM (
...> SELECT ID a, Price b FROM OrderTest WHERE Price < 200
...> UNION
...> SELECT ID a, Price b FROM OrderTest WHERE Price > 500
...> )
...> ORDER BY a IS 0, b;
a|b
3|0.0
4|25.0
1|50.0
2|75.0
5|100.0
7|1000.0
8|10000.0
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
