This works:
SELECT
1 AS col
ORDER BY
col > 0 DESC;
The following fails with "Error: 1st ORDER BY term does not match any
column in the result set."
SELECT
1 AS col
UNION ALL
SELECT
0 AS col
ORDER BY
col > 0 DESC;
I've read "The ORDER BY clause" of [1] and I *think* that the following
is what I am running into:
"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. "
If so, that paragraph could perhaps be written differently, more
clearly indicating that ORDER BY terms can *only* be raw column names
with no complex expressions. One could also perhaps explicitly suggest
what appears to be a valid workaround:
SELECT
*
FROM
(SELECT
1 AS col,
1 > 0 AS truth
UNION ALL
SELECT
0 AS col,
0 > 0 AS truth
) x
ORDER BY
x.col > 0 DESC;
[1] https://www.sqlite.org/lang_select.html
--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
