My God My God - 3 years ago 140
Android Question

Order by term not in result set

I am getting the following error while executing query in SQLite:

4th ORDER BY term does not match any column in the result set


My query:

select * from Users where deleted != 1 AND DATE(created) <= '2013-10-09' and
DATE(created) >= '2013-09-09T12:41:55.261Z' UNION select * from Users where deleted != 1 AND
owner NOT IN(Select userid from Customer)
order by Type DESC,License ASC,NUM ASC,"MCTD#" ASC,Name DESC;


However the equivalent with a single query i.e. without the UNION seems to work
fine.

Is there any restriction that ORDER BY terms must be in the result
set? And if so why doesn't it kick in without the UNION?

I used to select all from Users, so it should take all the columns from the User table. Then why this exception ?

CL. CL.
Answer Source

If the name of your column is MCTD%23, then you must use exactly that name to refer to the column. % escapes are a feature of the HTTP protocol and are not used in SQL.

The reason that "MCTD#" appears to work without UNION is that it does not actually refer to a column. When you are not using UNION, you can use arbitrary expressions in the ORDER BY clause. If a quoted column name is not found, then for compatibility with MySql, "MCTD#" is interpreted as 'MCTD#', which is just a constant string value that does not actually affect the sorting.

With UNION, ORDER BY does not allow arbitrary expressions but only actual column names from the result set, so it is not possible to interpret "MCTD#" as a string value.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download