mysql: Unknown Column Error in Query

Question

I was writing this query into a shell script so all the double quotes are escaped and there timestamps are variables.

This query works if i don't use the "order by" on terminal.

also there is a strange thing that if the execute the query part in the PHPMYADMIN then then "order by" works. but if i run this query in terminal it gives me UNKNOWN COLUMN QID error

SELECT 'GUIDE-NAME','QUESTION-ID','ACTION','TIMESTAMPS' 
UNION 
SELECT 
SUBSTRING(B.msg,LOCATE('q_id',B.msg)+5, (LOCATE('\"',B.msg)+2) ) AS qid, B.ts 
FROM TABLE B, TABLE A 
WHERE 
       LOCATE('q_id',B.msg) > 0 
       AND A.g_id=B.g_id and B.ts > from_unixtime($DateWeekBefore) 
       AND B.action 
       IN(\"review_question\",\"send_back",\"send_review\",\"publish\",\"quiet_publish\")  
ORDER BY qid 
INTO OUTFILE '/tmp/$vr9$DateWeek.csv' 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '\"' 
LINES TERMINATED BY '\n'

Answer 1

MySQL tries to apply order by on all query, not on a sub-select. In order to separate sub-selects use brackets:

(select ....) union (select.... order by something)

It seems that INTO OUTFILE can't be used directly in unions, so we need to make a union as a subquery:

SELECT * FROM ( 
    (
        SELECT 'GUIDE-NAME','QUESTION-ID','ACTION','TIMESTAMPS'
    ) UNION (
        SELECT.... ORDER BY qid
    ) 
) a 
INTO OUTFILE ....