Mike Mike - 11 months ago 36
MySQL Question

Is it possible to change the order by "DESC/ASC" in a case statment - MySql

I have a select statement with a order by command. Now the order by command has a case statment based on the status of the record it sort by a different column. However, I need to also the order by DESC if the status = 1 else order by ASC.

How can I do this?

This is my current statement

SELECT ph.phone_call_id AS id, ph.call_subject AS callSubject,
ph.trigger_on AS triggerOn,
IFNULL(ph.last_attempt_on, "") last_attempt_on,
ind.name AS industry,
ac.account_name AS accountName
FROM phone_calls AS ph
INNER JOIN accounts AS ac ON ph.account_id = ac.account_id
INNER JOIN industries AS ind ON ind.industry_id = ac.industry_id
INNER JOIN call_codes AS cc ON ph.call_code_id = cc.call_code_id
WHERE ac.status = 1
AND ph.status = '.$call_status.'
AND ph.owner_id = '. USER_ID .'
AND ac.do_not_call = 0
ORDER BY CASE WHEN ph.status = 1 THEN ph.trigger_on ELSE ph.last_attempt_on END

Answer Source

Is this what you want?

ORDER BY (CASE WHEN ph.status = 1 THEN ph.trigger_on  end) DESC,
         (case when ph.status <> 1 then ph.last_attempt_on END) ASC