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
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